DROP TABLE IF EXISTS #acutespecialty;
DROP TABLE IF EXISTS #appointment_tmp;
DROP TABLE IF EXISTS #appointment_pre2016;
DROP TABLE IF EXISTS #appointment_post2016;
DROP TABLE IF EXISTS #appointment;
DROP TABLE IF EXISTS #appt_visits;
DROP TABLE IF EXISTS #lastvisit;
DROP TABLE IF EXISTS #appt_visits_unique
DROP TABLE IF EXISTS #cohort;
DROP TABLE IF EXISTS #cohort_final;
DROP TABLE IF EXISTS #primarycare_teams;
DROP TABLE IF EXISTS #MH_icd9;
DROP TABLE IF EXISTS #MH_icd10;
DROP TABLE IF EXISTS #PH_icd9;
DROP TABLE IF EXISTS #PH_icd10;

DROP TABLE IF EXISTS #mh_inpat;
DROP TABLE IF EXISTS #mh_ed;
DROP TABLE IF EXISTS #mh_pit;
DROP TABLE IF EXISTS #mh_cms;
DROP TABLE IF EXISTS #ph_inpat;
DROP TABLE IF EXISTS #ph_ed;
DROP TABLE IF EXISTS #ph_pit;
DROP TABLE IF EXISTS #ph_cms;
DROP TABLE IF EXISTS #mh_agg;
DROP TABLE IF EXISTS #ph_agg;
DROP TABLE IF EXISTS #threeyear_outcomes;

SELECT DISTINCT ICD9SID, ICD9Code INTO #MH_icd9 FROM OMHSP_PERC_Share.DOEx.Lookup_ICD9
WHERE AmphetamineUseDisorder=1 OR AUD=1 OR AUD_ORM=1 OR BIPOLAR=1 OR Cannabis=1 OR CannabisUD_HallucUD=1 OR COCNdx=1 OR CocaineUD_AmphUD=1 OR DEMENTIA=1 OR DEPRESS=1 OR DEPRESS_EBP=1 OR MDD=1 OR OpioidOverdose=1 OR OtherMH=1 OR OUD=1 OR Psych=1 OR PTSD=1 OR SAE_Acet=1 OR SAE_sed=1 OR Schiz=1 OR SedativeUseDisorder=1 OR SMI=1 OR SUD_NoOUD_NoAUD=1
OR OtherSUD_RiskModel=1 OR Suicide=1 OR SUD_Active_Dx=1;

SELECT DISTINCT ICD10SID, ICD10Code INTO #MH_icd10 FROM OMHSP_PERC_Share.DOEx.Lookup_ICD10
WHERE AmphetamineUseDisorder=1 OR AUD=1 OR AUD_ORM=1 OR BIPOLAR=1 OR Cannabis=1 OR CannabisUD_HallucUD=1 OR COCNdx=1 OR CocaineUD_AmphUD=1 OR DEMENTIA=1 OR DEPRESS=1 OR DEPRESS_EBP=1 OR MDD=1 OR OpioidOverdose=1 OR OtherMH=1 OR OUD=1 OR Psych=1 OR PTSD=1 OR SAE_Acet=1 OR SAE_sed=1 OR Schiz=1 OR SedativeUseDisorder=1 OR SMI=1 OR SUD_NoOUD_NoAUD=1
OR OtherSUD_RiskModel=1 OR Suicide=1 OR SUD_Active_Dx=1;

SELECT DISTINCT ICD9SID, ICD9Code INTO #PH_icd9 FROM CDWWork.Dim.ICD9
WHERE TRY_CAST(SUBSTRING(ICD9Code, 1, 3) AS INT)>=390 AND TRY_CAST(SUBSTRING(ICD9Code, 1, 3) AS INT)<=459

SELECT DISTINCT ICD10SID, ICD10Code INTO #PH_icd10 FROM CDWWork.Dim.ICD10
WHERE ICD10Code LIKE 'I%'

SELECT DISTINCT Team, TeamSID, MaxNumberOfPatients, SpecialtyPACT=0 INTO #primarycare_teams FROM CDWWork.Dim.Team
WHERE CanActAsAPCTeamFlag='Y' AND TeamPurpose='PRIMARY CARE'
-- from VHA PACT Handbook: Specialty Care:
AND ServiceSection NOT LIKE '%GERI%' AND ServiceSection NOT LIKE '%GEC%' AND ServiceSection NOT LIKE '%EXTENDED%' AND Team NOT LIKE '%*GERI*%'
AND ServiceSection NOT LIKE '%HOME%' AND Team NOT LIKE '%*HBPC*%'
AND ServiceSection NOT LIKE '%HOME%' AND Team NOT LIKE '%*H*%'
AND ServiceSection NOT LIKE '%INFECT%' AND Team NOT LIKE '%*ID*%'
AND ServiceSection NOT LIKE '%POST-DEPLOY%' AND Team NOT LIKE '%*PD*%'
AND ServiceSection NOT LIKE '%RENAL%' AND ServiceSection NOT LIKE '%DIAL%' AND Team NOT LIKE '%*REN*%'
AND ServiceSection NOT LIKE '%MENTAL%' AND Team NOT LIKE '%*SMI*%'
AND ServiceSection NOT LIKE '%INJURY%' AND Team NOT LIKE '%*SCI/D*%'
AND ServiceSection NOT LIKE '%WOMEN%' AND Team NOT LIKE '%*MH*%'
-- next four are misc from my own observation of the data
AND ServiceSection NOT LIKE '%SUBST%'
AND ServiceSection NOT LIKE '%AMB%' 
AND ServiceSection NOT LIKE '%PSYCH%' 
AND ServiceSection NOT LIKE '%NEUROLO%'

SELECT DISTINCT SpecialtySID INTO #acutespecialty FROM CDWWork.Dim.Specialty
WHERE SPECIALTY IN ('ANESTHESIOLOGY', 'CARDIAC INTENSIVE CARE UNIT', 'CARDIAC SURGERY', 'CARDIAC-STEP DOWN UNIT', 'CARDIOLOGY', 'DERMATOLOGY', 'EAR, NOSE, THROAT (ENT)',
'ENDOCRINOLOGY', 'EPILEPSY CENTER', 'GASTROENTEROLOGY', 'GEM ACUTE MEDICINE', 'GEN MEDICINE (ACUTE)', 'GENERAL SURGERY', 'GENERAL(ACUTE MEDICINE)',
'HEMATOLOGY/ONCOLOGY', 'INTERMEDIATE MEDICINE', 'MEDICAL ICU', 'MEDICAL STEP DOWN', 'METABOLIC', 'NEUROLOGY', 'NEUROSURGERY', 'OB/GYN', 'OPHTHALMOLOGY',
'ORAL SURGERY', 'ORTHOPEDIC', 'PERIPHERAL VASCULAR', 'PLASTIC SURGERY', 'PODIATRY', 'PROCTOLOGY', 'PULMONARY, NON-TB', 'PULMONARY, TUBERCULOSIS', 
'STROKE UNIT', 'SURGICAL ICU', 'SURGICAL STEPDOWN', 'TELEMETRY', 'TORACIC SURGERY', 'TRANSPLANTATION', 'UROLOGY', 'VASCULAR', 'z GEM ACUTE MEDICINE',
'zCARDIAC-STEP DOWN UNIT', 'zCARDIOLOGY',' zDERMATOLOGY', 'zENDOCRINOLOGY', 'zEPILEPSY CENTER', 'zGASTROENTEROLOGY', 'zGENERAL(ACUTE MEDICINE)', 
'zHEMATOLOGY/ONCOLOGY', 'zMETABOLIC',' zNEUROLOGY', 'zNEURSURGERY', 'zOPHTHALMOLOGY', 'zORTHOPEDIC', 'zPERIPHERAL VASCULAR', 'zPODIATRY', 'zPROCTOLOGY', 'zPULMONARY, NON-TB',
'zPULMONARY, TUBERCULOSIS', 'zSTROKE UNIT', 'zSURGICAL ICU', 'zTELEMETRY', 'zUROLOGY', 'ZZPULMONARY DISEASE', 
'DOD BEDS IN VA FACILITY', 'GEM INTERMEDIATE CARE', 'GEM PSYCHIATRIC BEDS', 'SPINAL CORD INJURY', 'ACUTE PSYCHIATRY (<45 DAYS)',
'ACUTE PSYCHIATRY (<45 DAYS)', 'GEN INTERMEDIATE PSYCH', 'SUBSTANCE ABUSE RES TRMT PROG', 'PSYCH RESID REHAB TRMT PROG', 'PSYCHIATRIC MENTALLY INFIRM', 'PTSD RESID REHAB PROG', 'SUBST ABUSE CWT/TRANS RESID')

/***

-- In the AppointmentRequest table there is only ONE appointment request (row) per PatientSID
-- ALL 1010-EZ form appointment requests that initiate a PACT relationship due to actual assignment
-- Starting in early 2016, PCMM was decomissioned and everything moved to RPCMM
-- Post-2016 uses OMHO_MHTC1.mh.RPCMM_Providers: a snapshot collected end of Oct 2017 (SELECT max(RelationshipStartDateTime) FROM OMHO_MHTC1.mh.RPCMM_Providers) by PERC (Shalini email) which has all current relationships at that point 
-- StaffSSN is of the PCMM listed person, not the visit

***/
SELECT
	pcmm.Sta3n, appt_req.PatientSID, AppointmentRequestDate, EnrolleeInstitutionSID, EnrolleeValidThroughDate, DisabilityDischarge1010EZFlag, PreviousVACareFlag, Combat1010EZFlag, AppointmentMadeDate, AppointmentMadeFromLocationDate, DesiredAppointmentDate, AppointmentDateTime, AppointmentStatus, appt.VisitSID, appt.LocationSID, SchedulingRequestType, NextAvailableAppointment, FollowUpVisitFlag, LengthOfAppointment, OverbookFlag, PurposeOfVisit,
	pcmm.TeamSID, RelationshipStartDate, RelationshipEndDate, ProviderSID, PrimaryProviderSID, pcmm.Team, ProviderType, Classification, AreaOfSpecialization, sc1.StopCode AS StopCode1, sc2.StopCode AS StopCode2, StaffSSN, 
	CASE WHEN LocationName LIKE '%INTAKE%' OR LocationName LIKE '%IN-TAKE%' THEN 1 ELSE 0 END AS term_intake,
	CASE WHEN LocationName LIKE '%ORIENT%' THEN 1 ELSE 0 END AS term_orientation,
	CASE WHEN LocationName LIKE '%EVAL%' AND LocationName LIKE '%NEW%' THEN 1 ELSE 0 END AS term_neweval
INTO #appointment_tmp
FROM CDWWork.Appt.AppointmentRequest AS appt_req
INNER JOIN CDWWork.Appt.Appointment AS appt
	ON appt_req.PatientSID=appt.PatientSID AND AppointmentMadeDate>=AppointmentRequestDate AND DATEDIFF(day, AppointmentRequestDate, AppointmentMadeDate)<=30
INNER JOIN CDWWork.Outpat.VProvider AS prov
	ON appt.VisitSID=prov.VisitSID
INNER JOIN CDWWork.Dim.ProviderType AS prov2
	ON prov.ProviderTypeSID=prov2.ProviderTypeSID
INNER JOIN CDWWork.Dim.Location AS loc
	ON appt.LocationSID=loc.LocationSID
INNER JOIN CDWWork.Dim.StopCode AS sc1
	ON loc.PrimaryStopCodeSID=sc1.StopCodeSID
LEFT OUTER JOIN CDWWork.Dim.StopCode AS sc2
	ON loc.SecondaryStopCodeSID=sc2.StopCodeSID
INNER JOIN CDWWork.PCMM.PatientProviders AS pcmm
	ON appt.PatientSID=pcmm.PatientSID AND CAST(pcmm.RelationshipStartDate AS DATE)>=AppointmentRequestDate AND (ABS(DATEDIFF(day, RelationshipStartDate, AppointmentDateTime))<=30 OR ABS(DATEDIFF(day, RelationshipStartDate, AppointmentRequestDate))<=30 OR PrimaryProviderSID=prov.ProviderSID)
INNER JOIN CDWWork.SStaff.SStaff AS staff
	ON pcmm.PrimaryProviderSID=staff.StaffSID
WHERE AppointmentRequestDate>='01-01-2005' AND AppointmentRequestDate<'03-01-2017' 
AND AppointmentRequest1010EZFlag='Y' -- fills out they want an appointment on 1010-EZ
AND AppointmentRequestStatus='F' -- the request gets filled (could end up being cancelled or no show etc)
AND (AppointmentStatus NOT IN ('C', 'CA', 'N', 'NA', 'PC', 'PCA') OR AppointmentStatus IS NULL)-- Appointments that are not cancelled
AND appt.VisitSID>0 --actual appointment occurs
AND LocationName NOT LIKE '%mh%' AND NOT MedicalService='PSYCHIATRY'
AND ProviderRole='PC Assignment' AND TeamPurpose='PRIMARY CARE' AND AssociateProviderFlag='N';

SELECT #appointment_tmp.* INTO #appointment_pre2016 FROM #appointment_tmp INNER JOIN #primarycare_teams
	ON #appointment_tmp.TeamSID=#primarycare_teams.TeamSID

SELECT
	pcmm.Sta3n, appt_req.PatientSID, AppointmentRequestDate, EnrolleeInstitutionSID, EnrolleeValidThroughDate, DisabilityDischarge1010EZFlag, PreviousVACareFlag, Combat1010EZFlag, AppointmentMadeDate, AppointmentMadeFromLocationDate, DesiredAppointmentDate, AppointmentDateTime, AppointmentStatus, appt.VisitSID, appt.LocationSID, SchedulingRequestType, NextAvailableAppointment, FollowUpVisitFlag, LengthOfAppointment, OverbookFlag, PurposeOfVisit,
	RPCMMTeamSID AS TeamSID, CAST(RelationshipStartDateTime AS DATE) AS RelationshipStartDate, CAST(RelationshipEndDateTime AS DATE) AS RelationshipEndDate, prov.ProviderSID, PrimaryProviderSID, 'NA' AS Team, ProviderType, Classification, AreaOfSpecialization, sc1.StopCode AS StopCode1, sc2.StopCode AS StopCode2, StaffSSN,
	CASE WHEN LocationName LIKE '%INTAKE%' OR LocationName LIKE '%IN-TAKE%' THEN 1 ELSE 0 END AS term_intake,
	CASE WHEN LocationName LIKE '%ORIENT%' THEN 1 ELSE 0 END AS term_orientation,
	CASE WHEN LocationName LIKE '%EVAL%' AND LocationName LIKE '%NEW%' THEN 1 ELSE 0 END AS term_neweval
INTO #appointment_post2016
FROM CDWWork.Appt.AppointmentRequest AS appt_req
INNER JOIN CDWWork.Appt.Appointment AS appt
	ON appt_req.PatientSID=appt.PatientSID AND AppointmentMadeDate>=AppointmentRequestDate AND DATEDIFF(day, AppointmentRequestDate, AppointmentMadeDate)<=30
INNER JOIN CDWWork.Outpat.VProvider AS prov
	ON appt.VisitSID=prov.VisitSID
INNER JOIN CDWWork.Dim.ProviderType AS prov2
	ON prov.ProviderTypeSID=prov2.ProviderTypeSID
INNER JOIN CDWWork.Dim.Location AS loc
	ON appt.LocationSID=loc.LocationSID
INNER JOIN CDWWork.Dim.StopCode AS sc1
	ON loc.PrimaryStopCodeSID=sc1.StopCodeSID
LEFT OUTER JOIN CDWWork.Dim.StopCode AS sc2
	ON loc.SecondaryStopCodeSID=sc2.StopCodeSID
INNER JOIN OMHO_MHTC1.mh.RPCMM_Providers AS pcmm
	ON appt.PatientSID=pcmm.PatientSID AND CAST(pcmm.RelationshipStartDateTime AS DATE)>=AppointmentRequestDate AND (ABS(DATEDIFF(day, RelationshipStartDateTime, AppointmentDateTime))<=30 OR ABS(DATEDIFF(day, RelationshipStartDateTime, AppointmentRequestDate))<=30 OR PrimaryProviderSID=prov.ProviderSID)
INNER JOIN CDWWork.SStaff.SStaff AS staff
	ON pcmm.PrimaryProviderSID=staff.StaffSID
WHERE AppointmentRequestDate>='01-01-2005' AND AppointmentRequestDate<'03-01-2017' 
--(AND PreviousVACareFlag='N' OR PreviousVACareFlag IS NULL)-- no prior care (like 90% of people with 1010EZ form flag, unclear how accurate this is of actual no prior care)
AND AppointmentRequest1010EZFlag='Y' -- fills out they want an appointment on 1010-EZ
AND AppointmentRequestStatus='F' -- the request gets filled (could end up being cancelled or no show etc)
AND (AppointmentStatus NOT IN ('C', 'CA', 'N', 'NA', 'PC', 'PCA') OR AppointmentStatus IS NULL)-- Appointments that are not cancelled
AND appt.VisitSID>0 --actual appointment occurs
AND LocationName NOT LIKE '%mh%' AND NOT MedicalService='PSYCHIATRY'
AND RPCMMTeamFocus IN ('Primary Care Only', 'Womens Health', 'Academic') AND RPCMMTeamCareType='PRIMARY CARE' AND PCMMAssociateProviderFlag='N';


-- COMBINE pre 2016 and post 2016
SELECT * INTO #appointment FROM #appointment_pre2016 UNION (SELECT * FROM #appointment_post2016);



-- Join appointment visit with DIAGNOSIS and VISIT
SELECT
	*
INTO #appt_visits
FROM(
	SELECT
		#appointment.*, visit.VisitDateTime, ICD9Code, NULL AS ICD10Code, PatientNumberOfDependents, PatientIncome, PatientFIPS, PatientPeriodOfService, visit.InstitutionSID, ProviderType, Classification, AreaOfSpecialization
	FROM #appointment
	INNER JOIN CDWWork.Outpat.Visit AS visit	
		ON #appointment.VisitSID=visit.VisitSID
	INNER JOIN CDWWork.Outpat.VDiagnosis AS diag
		ON #appointment.VisitSID=diag.VisitSID
	INNER JOIN CDWWork.Dim.ICD9 AS icd
		ON diag.ICD9SID=icd.ICD9SID
	INNER JOIN CDWWork.Dim.ProviderType AS prov
		ON #appointment.ProviderTypeSID=prov.ProviderTypeSID
	WHERE diag.ICD9SID>0 AND PrimarySecondary='P' AND visit.VisitDateTime<='2015-10-01' AND visit.VisitSID>0
	UNION(
	SELECT
		#appointment.*, visit.VisitDateTime, NULL AS ICD9Code, ICD10Code, PatientNumberOfDependents, PatientIncome, PatientFIPS, PatientPeriodOfService, visit.InstitutionSID, ProviderType, Classification, AreaOfSpecialization
	FROM #appointment
	INNER JOIN CDWWork.Outpat.Visit AS visit	
		ON #appointment.VisitSID=visit.VisitSID
	INNER JOIN CDWWork.Outpat.VDiagnosis AS diag
		ON #appointment.VisitSID=diag.VisitSID
	INNER JOIN CDWWork.Dim.ICD10 AS icd
		ON diag.ICD10SID=icd.ICD10SID
	INNER JOIN CDWWork.Dim.ProviderType AS prov
		ON #appointment.ProviderTypeSID=prov.ProviderTypeSID
	WHERE diag.ICD10SID>0 AND PrimarySecondary='P' AND visit.VisitDateTime>='2015-10-01' AND visit.VisitSID>0
	)
) AS z


--- Create cohort with one per patient
SELECT 
	*
INTO #appt_visits_unique
FROM(
SELECT 
	*, ROW_NUMBER() OVER(PARTITION BY PatientICN ORDER BY InPCMM DESC, RelationshipStartDate ASC, ProviderMatch DESC, InstitutionMatch DESC, AppointmentDateTime ASC) AS row_num
FROM(
SELECT 
	PatientICN, #appt_visits.*, Gender, BirthDateTime, Race, ScrSSN, MaritalStatus, ServiceConnectedFlag, Religion,
	CASE WHEN RelationshipStartDate IS NOT NULL THEN 1 ELSE 0 END AS InPCMM,
	CASE WHEN EnrolleeInstitutionSID=InstitutionSID THEN 1 ELSE 0 END AS InstitutionMatch,
	CASE WHEN ProviderSID=PrimaryProviderSID THEN 1 ELSE 0 END AS ProviderMatch,
	MaxNumberOfPatients, COALESCE(SpecialtyPACT, 1) AS SpecialtyPACT
FROM #appt_visits
INNER JOIN CDWWork.SPatient.SPatient AS pat
	ON #appt_visits.PatientSID=pat.PatientSID
LEFT OUTER JOIN CDWWork.PatSub.PatientRace AS race
	ON #appt_visits.PatientSID=race.PatientSID
LEFT OUTER JOIN #primarycare_teams
	ON #appt_visits.TeamSID=#primarycare_teams.TeamSID
) AS z
) AS zz
WHERE row_num=1


--- Final cohort: when and what was their most recent VA prior visit (abbreviate this PV)
SELECT
	*
INTO #lastvisit
FROM(
SELECT 
	*, ROW_NUMBER() OVER(PARTITION BY PatientICN ORDER BY PV_DateDiff ASC) AS row_num2
FROM(
SELECT #appt_visits_unique.PatientICN, DATEDIFF(day, visit.VisitDateTime, #appt_visits_unique.AppointmentRequestDate) AS PV_DateDiff, StopCode AS PV_StopCode, 'outpat' AS PV_OutInp, staff.StaffSSN AS PV_StaffSSN
FROM CDWWork.Outpat.Visit AS visit
INNER JOIN CDWWork.SPatient.SPatient AS pat
	ON visit.PatientSID=pat.PatientSID
INNER JOIN #appt_visits_unique
	ON #appt_visits_unique.PatientICN=pat.PatientICN AND visit.VisitDateTime<#appt_visits_unique.AppointmentRequestDate
INNER JOIN CDWWork.Dim.StopCode AS sc
	ON visit.PrimaryStopCodeSID=sc.StopCodeSID
INNER JOIN CDWWork.Outpat.VProvider AS prov
	ON visit.VisitSID=prov.VisitSID
INNER JOIN CDWWork.SStaff.SStaff AS staff
	ON prov.ProviderSID=staff.StaffSID
WHERE visit.LocationSID>0
UNION(
SELECT #appt_visits_unique.PatientICN, DATEDIFF(day, visit.DischargeDateTime, #appt_visits_unique.AppointmentRequestDate) AS PV_DateDiff, -1 AS PV_StopCode, 'inpat' AS PV_OutInp, staff.StaffSSN AS PV_StaffSSN
FROM CDWWork.Inpat.Inpatient AS visit
INNER JOIN CDWWork.SPatient.SPatient AS pat
	ON visit.PatientSID=pat.PatientSID
INNER JOIN #appt_visits_unique
	ON #appt_visits_unique.PatientICN=pat.PatientICN AND visit.AdmitDateTime<=#appt_visits_unique.AppointmentRequestDate
INNER JOIN CDWWork.SStaff.SStaff AS staff
	ON visit.ProviderSID=staff.StaffSID
)
) AS z
) AS zz
WHERE row_num2=1


SELECT #appt_visits_unique.*,  PV_DateDiff, PV_StopCode, PV_OutInp, PV_StaffSSN, StaffSSN INTO #cohort_final FROM #appt_visits_unique 
INNER JOIN CDWWork.SStaff.SStaff AS staff
	ON #appt_visits_unique.Coalesced_ProviderSID=staff.StaffSID
LEFT OUTER JOIN #lastvisit ON #appt_visits_unique.PatientICN=#lastvisit.PatientICN

/** 

		OUTCOMES

					***/



-- ed
SELECT DISTINCT
	PatientICN, OutcomeDate, relative_days, type
INTO #mh_ed
FROM (
	SELECT 
		PatientICN, CAST(VisitDateTime AS DATE) AS OutcomeDate, relative_days, type
	FROM(
		SELECT #cohort_final.PatientICN, outpat.VisitDateTime, DATEDIFF(day, #cohort_final.AppointmentRequestDate, outpat.VisitDateTime) AS relative_days,
		CASE WHEN StopCode='130' THEN 'ED' WHEN StopCode='131' THEN 'UCC' END AS type
		FROM CDWWork.Outpat.VDiagnosis AS outpat
		INNER JOIN CDWWork.Outpat.Visit AS visit
			ON outpat.VisitSID=visit.VisitSID
		INNER JOIN #MH_icd9
			ON outpat.ICD9SID=#MH_icd9.ICD9SID
		INNER JOIN CDWWork.SPatient.SPatient AS pat
			ON outpat.PatientSID=pat.PatientSID
		INNER JOIN #cohort_final
			ON pat.PatientICN=#cohort_final.PatientICN
		INNER JOIN CDWWork.Dim.StopCode AS sc
			ON visit.PrimaryStopCodeSID=sc.StopCodeSID
		WHERE outpat.VisitDateTime<'2015-10-01' AND DATEDIFF(day, #cohort_final.AppointmentRequestDate, outpat.VisitDateTime)>=(-365) AND DATEDIFF(day, #cohort_final.AppointmentRequestDate, outpat.VisitDateTime)<=(365*3) AND NOT DATEDIFF(day, #cohort_final.AppointmentRequestDate, outpat.VisitDateTime)=0
		AND StopCode IN ('130', '131')
	) AS a
	UNION(
		SELECT #cohort_final.PatientICN, outpat.VisitDateTime, DATEDIFF(day, #cohort_final.AppointmentRequestDate, outpat.VisitDateTime) AS relative_days,
		CASE WHEN StopCode='130' THEN 'ED' WHEN StopCode='131' THEN 'UCC' END AS type
		FROM CDWWork.Outpat.VDiagnosis AS outpat
		INNER JOIN CDWWork.Outpat.Visit AS visit
			ON outpat.VisitSID=visit.VisitSID
		INNER JOIN #MH_icd10
			ON outpat.ICD10SID=#MH_icd10.ICD10SID
		INNER JOIN CDWWork.SPatient.SPatient AS pat
			ON outpat.PatientSID=pat.PatientSID
		INNER JOIN #cohort_final
			ON pat.PatientICN=#cohort_final.PatientICN
		INNER JOIN CDWWork.Dim.StopCode AS sc
			ON visit.PrimaryStopCodeSID=sc.StopCodeSID
		WHERE outpat.VisitDateTime>='2015-10-01' AND DATEDIFF(day, #cohort_final.AppointmentRequestDate, outpat.VisitDateTime)>=(-365) AND DATEDIFF(day, #cohort_final.AppointmentRequestDate, outpat.VisitDateTime)<=(365*3) AND NOT DATEDIFF(day, #cohort_final.AppointmentRequestDate, outpat.VisitDateTime)=0
		AND StopCode IN ('130', '131')
	)
) AS z;

SELECT DISTINCT
	PatientICN, OutcomeDate, relative_days, type
INTO #ph_ed
FROM (
	SELECT 
		PatientICN, CAST(VisitDateTime AS DATE) AS OutcomeDate, relative_days, type
	FROM(
		SELECT #cohort_final.PatientICN, outpat.VisitDateTime, DATEDIFF(day, #cohort_final.AppointmentRequestDate, outpat.VisitDateTime) AS relative_days,
		CASE WHEN StopCode='130' THEN 'ED' WHEN StopCode='131' THEN 'UCC' END AS type
		FROM CDWWork.Outpat.VDiagnosis AS outpat
		INNER JOIN CDWWork.Outpat.Visit AS visit
			ON outpat.VisitSID=visit.VisitSID
		INNER JOIN #PH_icd9
			ON outpat.ICD9SID=#PH_icd9.ICD9SID
		INNER JOIN CDWWork.SPatient.SPatient AS pat
			ON outpat.PatientSID=pat.PatientSID
		INNER JOIN #cohort_final
			ON pat.PatientICN=#cohort_final.PatientICN
		INNER JOIN CDWWork.Dim.StopCode AS sc
			ON visit.PrimaryStopCodeSID=sc.StopCodeSID
		WHERE outpat.VisitDateTime<'2015-10-01' AND DATEDIFF(day, #cohort_final.AppointmentRequestDate, outpat.VisitDateTime)>=(-365) AND DATEDIFF(day, #cohort_final.AppointmentRequestDate, outpat.VisitDateTime)<=(365*3) AND NOT DATEDIFF(day, #cohort_final.AppointmentRequestDate, outpat.VisitDateTime)=0
		AND StopCode IN ('130', '131')
	) AS a
	UNION(
		SELECT #cohort_final.PatientICN, outpat.VisitDateTime, DATEDIFF(day, #cohort_final.AppointmentRequestDate, outpat.VisitDateTime) AS relative_days,
		CASE WHEN StopCode='130' THEN 'ED' WHEN StopCode='131' THEN 'UCC' END AS type
		FROM CDWWork.Outpat.VDiagnosis AS outpat
		INNER JOIN CDWWork.Outpat.Visit AS visit
			ON outpat.VisitSID=visit.VisitSID
		INNER JOIN #PH_icd10
			ON outpat.ICD10SID=#PH_icd10.ICD10SID
		INNER JOIN CDWWork.SPatient.SPatient AS pat
			ON outpat.PatientSID=pat.PatientSID
		INNER JOIN #cohort_final
			ON pat.PatientICN=#cohort_final.PatientICN
		INNER JOIN CDWWork.Dim.StopCode AS sc
			ON visit.PrimaryStopCodeSID=sc.StopCodeSID
		WHERE outpat.VisitDateTime>='2015-10-01' AND DATEDIFF(day, #cohort_final.AppointmentRequestDate, outpat.VisitDateTime)>=(-365) AND DATEDIFF(day, #cohort_final.AppointmentRequestDate, outpat.VisitDateTime)<=(365*3) AND NOT DATEDIFF(day, #cohort_final.AppointmentRequestDate, outpat.VisitDateTime)=0
		AND StopCode IN ('130', '131')
	)
) AS z;


-- acute inpatient hospitalizations

SELECT DISTINCT
	PatientICN, OutcomeDate, relative_days, 'HOSP' AS type
INTO #mh_inpat
FROM (
	SELECT 
		PatientICN, CAST(DischargeDateTime AS DATE) AS OutcomeDate, relative_days
	FROM(
		SELECT #cohort_final.PatientICN, DischargeDateTime, DATEDIFF(day, #cohort_final.AppointmentRequestDate, DischargeDateTime) AS relative_days
		FROM CDWWork.Inpat.InpatientDiagnosis AS inp
		INNER JOIN CDWWork.Inpat.SpecialtyTransfer AS transfer
			ON inp.InpatientSID=transfer.InpatientSID
		INNER JOIN CDWWork.Inpat.PresentOnAdmission AS poa
			ON inp.InpatientSID=poa.InpatientSID
		INNER JOIN #acutespecialty
			ON transfer.TreatingSpecialtySID=#acutespecialty.SpecialtySID
		INNER JOIN #MH_icd9
			ON poa.ICD9SID=#MH_icd9.ICD9SID
		INNER JOIN CDWWork.SPatient.SPatient AS pat
			ON inp.PatientSID=pat.PatientSID
		INNER JOIN #cohort_final
			ON pat.PatientICN=#cohort_final.PatientICN
		WHERE DischargeDateTime<'2015-10-01' AND PresentOnAdmissionCode='Y'
		AND DATEDIFF(day, #cohort_final.AppointmentRequestDate, DischargeDateTime)>=(-365) AND DATEDIFF(day, #cohort_final.AppointmentRequestDate, DischargeDateTime)<=(365*3) AND NOT DATEDIFF(day, #cohort_final.AppointmentRequestDate, DischargeDateTime)=0
	) AS a
	UNION(
		SELECT #cohort_final.PatientICN, DischargeDateTime, DATEDIFF(day, #cohort_final.AppointmentRequestDate, DischargeDateTime) AS relative_days
		FROM CDWWork.Inpat.InpatientDiagnosis AS inp
		INNER JOIN CDWWork.Inpat.PresentOnAdmission AS poa
			ON inp.InpatientSID=poa.InpatientSID
		INNER JOIN CDWWork.Inpat.SpecialtyTransfer AS transfer
			ON inp.InpatientSID=transfer.InpatientSID
		INNER JOIN #acutespecialty
			ON transfer.TreatingSpecialtySID=#acutespecialty.SpecialtySID
		INNER JOIN #MH_icd10
			ON poa.ICD10SID=#MH_icd10.ICD10SID
		INNER JOIN CDWWork.SPatient.SPatient AS pat
			ON inp.PatientSID=pat.PatientSID
		INNER JOIN #cohort_final
			ON pat.PatientICN=#cohort_final.PatientICN
		WHERE DischargeDateTime>='2015-10-01' AND PresentOnAdmissionCode='Y'
		AND DATEDIFF(day, #cohort_final.AppointmentRequestDate, DischargeDateTime)>=(-365) AND DATEDIFF(day, #cohort_final.AppointmentRequestDate, DischargeDateTime)<=(365*3) AND NOT DATEDIFF(day, #cohort_final.AppointmentRequestDate, DischargeDateTime)=0
	)
) AS z;

SELECT DISTINCT
	PatientICN, OutcomeDate, relative_days, 'HOSP' AS type
INTO #ph_inpat
FROM (
	SELECT 
		PatientICN, CAST(DischargeDateTime AS DATE) AS OutcomeDate, relative_days
	FROM(
		SELECT #cohort_final.PatientICN, DischargeDateTime, DATEDIFF(day, #cohort_final.AppointmentRequestDate, DischargeDateTime) AS relative_days
		FROM CDWWork.Inpat.InpatientDiagnosis AS inp
		INNER JOIN CDWWork.Inpat.SpecialtyTransfer AS transfer
			ON inp.InpatientSID=transfer.InpatientSID
		INNER JOIN CDWWork.Inpat.PresentOnAdmission AS poa
			ON inp.InpatientSID=poa.InpatientSID
		INNER JOIN #acutespecialty
			ON transfer.TreatingSpecialtySID=#acutespecialty.SpecialtySID
		INNER JOIN #PH_icd9
			ON poa.ICD9SID=#PH_icd9.ICD9SID
		INNER JOIN CDWWork.SPatient.SPatient AS pat
			ON inp.PatientSID=pat.PatientSID
		INNER JOIN #cohort_final
			ON pat.PatientICN=#cohort_final.PatientICN
		WHERE DischargeDateTime<'2015-10-01' AND PresentOnAdmissionCode='Y'
		AND DATEDIFF(day, #cohort_final.AppointmentRequestDate, DischargeDateTime)>=(-365) AND DATEDIFF(day, #cohort_final.AppointmentRequestDate, DischargeDateTime)<=(365*3) AND NOT DATEDIFF(day, #cohort_final.AppointmentRequestDate, DischargeDateTime)=0
	) AS a
	UNION(
		SELECT #cohort_final.PatientICN, DischargeDateTime, DATEDIFF(day, #cohort_final.AppointmentRequestDate, DischargeDateTime) AS relative_days
		FROM CDWWork.Inpat.InpatientDiagnosis AS inp
		INNER JOIN CDWWork.Inpat.PresentOnAdmission AS poa
			ON inp.InpatientSID=poa.InpatientSID
		INNER JOIN CDWWork.Inpat.SpecialtyTransfer AS transfer
			ON inp.InpatientSID=transfer.InpatientSID
		INNER JOIN #acutespecialty
			ON transfer.TreatingSpecialtySID=#acutespecialty.SpecialtySID
		INNER JOIN #PH_icd10
			ON poa.ICD10SID=#PH_icd10.ICD10SID
		INNER JOIN CDWWork.SPatient.SPatient AS pat
			ON inp.PatientSID=pat.PatientSID
		INNER JOIN #cohort_final
			ON pat.PatientICN=#cohort_final.PatientICN
		WHERE DischargeDateTime>='2015-10-01' AND PresentOnAdmissionCode='Y'
		AND DATEDIFF(day, #cohort_final.AppointmentRequestDate, DischargeDateTime)>=(-365) AND DATEDIFF(day, #cohort_final.AppointmentRequestDate, DischargeDateTime)<=(365*3) AND NOT DATEDIFF(day, #cohort_final.AppointmentRequestDate, DischargeDateTime)=0
	)
) AS z;


-- COMMUNITY: PIT AND FEE BASIS

SELECT
	PatientICN, OutcomeDate, relative_days, type
INTO #mh_pit
FROM (
	SELECT 
		PatientICN, CAST(OutcomeDate AS DATE) AS OutcomeDate, relative_days, type
	FROM(
		SELECT
			#cohort_final.PatientICN, c.ServiceFromDate AS OutcomeDate, DATEDIFF(day, AppointmentRequestDate, c.ServiceFromDate) AS relative_days, 
			CASE 
				WHEN PitPlaceOfService='Emergency Room - Hospital' THEN 'COMM-ED' WHEN PitPlaceOfService='Urgent Care Facility' THEN 'COMM-UCC' ELSE 'COMM-HOSP'
			END AS type
		FROM CDWWork.PIT.PITClaim AS a
		INNER JOIN CDWWork.PIT.PitProfessionalClaim AS b
			ON a.PITClaimSID=b.PITClaimSID
		INNER JOIN CDWWork.PIT.PitProfessionalClaimDetails AS c
			ON b.PITClaimSID=c.PITCLaimSID
		INNER JOIN CDWWork.PIT.PitClaimDiagnosis AS d
			ON b.PITProfessionalClaimSID=d.PITProfessionalClaimSID
		INNER JOIN CDWWork.NDIM.PITDiagnosisCode AS dim
			ON d.PITDiagnosisCodeSID=dim.PITDiagnosisCodeSID
		INNER JOIN #MH_icd9
			ON dim.PITDiagnosisCode=#MH_icd9.ICD9Code
		INNER JOIN (SELECT PatientICN, PitPatientSID FROM CDWWork.SVeteran.PitPatient WHERE PitPatientSID>0 AND NOT PatientICN='*Unknown at this time*') AS pat
			ON c.PitPatientSID=pat.PitPatientSID
		INNER JOIN #cohort_final
			ON pat.PatientICN=#cohort_final.PatientICN
		INNER JOIN CDWWork.NDim.PITPlaceOfService AS pos
			ON b.PITPlaceOfServiceSID=pos.PITPlaceOfServiceSID
		WHERE a.CurrentFlag='Y' AND DATEDIFF(day, #cohort_final.AppointmentRequestDate, c.ServiceFromDate)>=(-365) AND DATEDIFF(day, #cohort_final.AppointmentRequestDate, c.ServiceFromDate)<=(365*3) AND NOT DATEDIFF(day, #cohort_final.AppointmentRequestDate, c.ServiceFromDate)=0
		AND PITPlaceOfService IN ('Inpatient Hospital', 'Urgent Care Facility', 'Emergency Room - Hospital', 'Inpatient Psychiatric Facility', 'Psychiatric Facility-Partial Hospitalization')--, 'Residential Substance Abuse Treatment Facility', 'Psychiatric Residential Treatment Center')
	) AS X
	UNION(
		SELECT
			#cohort_final.PatientICN, c.ServiceFromDate AS OutcomeDate, DATEDIFF(day, AppointmentRequestDate, c.ServiceFromDate) AS relative_days, 
			CASE 
				WHEN PitPlaceOfService='Emergency Room - Hospital' THEN 'COMM-ED' WHEN PitPlaceOfService='Urgent Care Facility' THEN 'COMM-UCC' ELSE 'COMM-HOSP'
			END AS type
		FROM CDWWork.PIT.PITClaim AS a
		INNER JOIN CDWWork.PIT.PitProfessionalClaim AS b
			ON a.PITClaimSID=b.PITClaimSID
		INNER JOIN CDWWork.PIT.PitProfessionalClaimDetails AS c
			ON b.PITClaimSID=c.PITCLaimSID
		INNER JOIN CDWWork.PIT.PitClaimDiagnosis AS d
			ON b.PITProfessionalClaimSID=d.PITProfessionalClaimSID
		INNER JOIN CDWWork.NDIM.PITDiagnosisCode AS dim
			ON d.PITDiagnosisCodeSID=dim.PITDiagnosisCodeSID
		INNER JOIN #MH_icd10
			ON dim.PITDiagnosisCode=#MH_icd10.ICD10Code
		INNER JOIN (SELECT PatientICN, PitPatientSID FROM CDWWork.SVeteran.PitPatient WHERE PitPatientSID>0 AND NOT PatientICN='*Unknown at this time*') AS pat
			ON c.PitPatientSID=pat.PitPatientSID
		INNER JOIN #cohort_final
			ON pat.PatientICN=#cohort_final.PatientICN
		INNER JOIN CDWWork.NDim.PITPlaceOfService AS pos
			ON b.PITPlaceOfServiceSID=pos.PITPlaceOfServiceSID
		WHERE a.CurrentFlag='Y' AND DATEDIFF(day, #cohort_final.AppointmentRequestDate, c.ServiceFromDate)>=(-365) AND DATEDIFF(day, #cohort_final.AppointmentRequestDate, c.ServiceFromDate)<=(365*3) AND NOT DATEDIFF(day, #cohort_final.AppointmentRequestDate, c.ServiceFromDate)=0
		AND PITPlaceOfService IN ('Inpatient Hospital', 'Urgent Care Facility', 'Emergency Room - Hospital', 'Inpatient Psychiatric Facility', 'Psychiatric Facility-Partial Hospitalization')--, 'Residential Substance Abuse Treatment Facility', 'Psychiatric Residential Treatment Center')
	)
	UNION(
		SELECT
			#cohort_final.PatientICN, c.ServiceDate AS OutcomeDate, DATEDIFF(day, AppointmentRequestDate, c.ServiceDate) AS relative_days, 
			CASE 
				WHEN PitPlaceOfService='Emergency Room - Hospital' THEN 'COMM-ED' WHEN PitPlaceOfService='Urgent Care Facility' THEN 'COMM-UCC' ELSE 'COMM-HOSP'
			END AS type
		FROM CDWWork.PIT.PITClaim AS a
		INNER JOIN CDWWork.PIT.PitInstitutionalClaim AS b
			ON a.PITClaimSID=b.PITClaimSID
		INNER JOIN CDWWork.PIT.PitInstitutionalClaimDetails AS c
			ON b.PITClaimSID=c.PITCLaimSID
		INNER JOIN CDWWork.PIT.PitClaimDiagnosis AS d
			ON b.PITInstitutionalClaimSID=d.PITInstitutionalClaimSID
		INNER JOIN CDWWork.NDIM.PITDiagnosisCode AS dim
			ON d.PITDiagnosisCodeSID=dim.PITDiagnosisCodeSID
		INNER JOIN #MH_icd9
			ON dim.PITDiagnosisCode=#MH_icd9.ICD9Code
		INNER JOIN (SELECT PatientICN, PitPatientSID FROM CDWWork.SVeteran.PitPatient WHERE PitPatientSID>0 AND NOT PatientICN='*Unknown at this time*') AS pat
			ON c.PitPatientSID=pat.PitPatientSID
		INNER JOIN #cohort_final
			ON pat.PatientICN=#cohort_final.PatientICN
		INNER JOIN CDWWork.NDim.PITPlaceOfService AS pos
			ON b.PITPlaceOfServiceSID=pos.PITPlaceOfServiceSID
		WHERE a.CurrentFlag='Y' AND DATEDIFF(day, #cohort_final.AppointmentRequestDate, c.ServiceDate)>=(-365) AND DATEDIFF(day, #cohort_final.AppointmentRequestDate, c.ServiceDate)<=(365*3) AND NOT DATEDIFF(day, #cohort_final.AppointmentRequestDate, c.ServiceDate)=0
		AND PITPlaceOfService IN ('Inpatient Hospital', 'Urgent Care Facility', 'Emergency Room - Hospital', 'Inpatient Psychiatric Facility', 'Psychiatric Facility-Partial Hospitalization')--, 'Residential Substance Abuse Treatment Facility', 'Psychiatric Residential Treatment Center')
	)
	UNION(
		SELECT
			#cohort_final.PatientICN, c.ServiceDate AS OutcomeDate, DATEDIFF(day, AppointmentRequestDate, c.ServiceDate) AS relative_days, 
			CASE 
				WHEN PitPlaceOfService='Emergency Room - Hospital' THEN 'COMM-ED' WHEN PitPlaceOfService='Urgent Care Facility' THEN 'COMM-UCC' ELSE 'COMM-HOSP'
			END AS type
		FROM CDWWork.PIT.PITClaim AS a
		INNER JOIN CDWWork.PIT.PitInstitutionalClaim AS b
			ON a.PITClaimSID=b.PITClaimSID
		INNER JOIN CDWWork.PIT.PitInstitutionalClaimDetails AS c
			ON b.PITClaimSID=c.PITCLaimSID
		INNER JOIN CDWWork.PIT.PitClaimDiagnosis AS d
			ON b.PITInstitutionalClaimSID=d.PITInstitutionalClaimSID
		INNER JOIN CDWWork.NDIM.PITDiagnosisCode AS dim
			ON d.PITDiagnosisCodeSID=dim.PITDiagnosisCodeSID
		INNER JOIN #MH_icd10
			ON dim.PITDiagnosisCode=#MH_icd10.ICD10Code
		INNER JOIN (SELECT PatientICN, PitPatientSID FROM CDWWork.SVeteran.PitPatient WHERE PitPatientSID>0 AND NOT PatientICN='*Unknown at this time*') AS pat
			ON c.PitPatientSID=pat.PitPatientSID
		INNER JOIN #cohort_final
			ON pat.PatientICN=#cohort_final.PatientICN
		INNER JOIN CDWWork.NDim.PITPlaceOfService AS pos
			ON b.PITPlaceOfServiceSID=pos.PITPlaceOfServiceSID
		WHERE a.CurrentFlag='Y' AND DATEDIFF(day, #cohort_final.AppointmentRequestDate, c.ServiceDate)>=(-365) AND DATEDIFF(day, #cohort_final.AppointmentRequestDate, c.ServiceDate)<=(365*3) AND NOT DATEDIFF(day, #cohort_final.AppointmentRequestDate, c.ServiceDate)=0
		AND PITPlaceOfService IN ('Inpatient Hospital', 'Urgent Care Facility', 'Emergency Room - Hospital', 'Inpatient Psychiatric Facility', 'Psychiatric Facility-Partial Hospitalization')--, 'Residential Substance Abuse Treatment Facility', 'Psychiatric Residential Treatment Center')
	)
	UNION(
		SELECT #cohort_final.PatientICN, CAST(InitialTreatmentDateTime AS date) AS OutcomeDate, DATEDIFF(day, AppointmentRequestDate, InitialTreatmentDateTime) AS relative_days,
		CASE WHEN IBPlaceOfService='EMERGENCY ROOM - HOSPITAL' THEN 'COMM-ED' WHEN IBPlaceOfService='URGENT CARE FACILITY' THEN 'COMM-UCC' ELSE 'COMM-HOSP' END AS type
		FROM CDWWork.Fee.FeeServiceProvided AS a
		INNER JOIN CDWWork.Fee.FeeInitialTreatment AS b
			ON a.FeeInitialTreatmentSID=b.FeeInitialTreatmentSID
		INNER JOIN #MH_icd9
				ON a.ICD9SID=#MH_icd9.ICD9SID
		INNER JOIN (SELECT PatientICN, PatientSID FROM CDWWork.Patient.Patient WHERE PatientSID>0 AND NOT PatientICN='*Unknown at this time*') AS pat
			ON a.PatientSID=pat.PatientSID
		INNER JOIN #cohort_final
			ON pat.PatientICN=#cohort_final.PatientICN
		INNER JOIN CDWWork.Dim.IBPlaceOfService AS pos
			ON a.IBPlaceOfServiceSID=pos.IBPlaceOfServiceSID
		WHERE DATEDIFF(day, #cohort_final.AppointmentRequestDate, InitialTreatmentDateTime)>=(-365) AND DATEDIFF(day, #cohort_final.AppointmentRequestDate, InitialTreatmentDateTime)<=(365*3) AND NOT DATEDIFF(day, #cohort_final.AppointmentRequestDate, InitialTreatmentDateTime)=0
		AND IBPlaceOfService IN ('EMERGENCY ROOM - HOSPITAL', 'URGENT CARE FACILITY', 'Hospital as Inpatient', 'INPATIENT HOSPITAL', 'INPATIENT PSYCHIATRIC FACILITY', 'PSY. FAC. PARTIAL HOSPITALIZATION')--, 'PSYCHIATRIC RESIDENTIAL TREATMENT CENTER', 'Resdential Substance Abuse Facility', 'RESIDENTIAL SUBSTANCE ABUSE TREATMENT FACILITY', 'BCBS RES. SUBSTANCE ABUSE TREATMENT FACILITY')
	)
	UNION(
		SELECT #cohort_final.PatientICN, CAST(InitialTreatmentDateTime AS date) AS OutcomeDate, DATEDIFF(day, AppointmentRequestDate, InitialTreatmentDateTime) AS relative_days,
		CASE WHEN IBPlaceOfService='EMERGENCY ROOM - HOSPITAL' THEN 'COMM-ED' WHEN IBPlaceOfService='URGENT CARE FACILITY' THEN 'COMM-UCC' ELSE 'COMM-HOSP' END AS type
		FROM CDWWork.Fee.FeeServiceProvided AS a
		INNER JOIN CDWWork.Fee.FeeInitialTreatment AS b
			ON a.FeeInitialTreatmentSID=b.FeeInitialTreatmentSID
		INNER JOIN #MH_icd10
				ON a.ICD10SID=#MH_icd10.ICD10SID
		INNER JOIN (SELECT PatientICN, PatientSID FROM CDWWork.Patient.Patient WHERE PatientSID>0 AND NOT PatientICN='*Unknown at this time*') AS pat
			ON a.PatientSID=pat.PatientSID
		INNER JOIN #cohort_final
			ON pat.PatientICN=#cohort_final.PatientICN
		INNER JOIN CDWWork.Dim.IBPlaceOfService AS pos
			ON a.IBPlaceOfServiceSID=pos.IBPlaceOfServiceSID
		WHERE DATEDIFF(day, #cohort_final.AppointmentRequestDate, InitialTreatmentDateTime)>=(-365) AND DATEDIFF(day, #cohort_final.AppointmentRequestDate, InitialTreatmentDateTime)<=(365*3) AND NOT DATEDIFF(day, #cohort_final.AppointmentRequestDate, InitialTreatmentDateTime)=0
		AND IBPlaceOfService IN ('EMERGENCY ROOM - HOSPITAL', 'URGENT CARE FACILITY', 'Hospital as Inpatient', 'INPATIENT HOSPITAL', 'INPATIENT PSYCHIATRIC FACILITY', 'PSY. FAC. PARTIAL HOSPITALIZATION')--, 'PSYCHIATRIC RESIDENTIAL TREATMENT CENTER', 'Resdential Substance Abuse Facility', 'RESIDENTIAL SUBSTANCE ABUSE TREATMENT FACILITY', 'BCBS RES. SUBSTANCE ABUSE TREATMENT FACILITY')

	)
) AS Z;

SELECT
	PatientICN, OutcomeDate, relative_days, type
INTO #ph_pit
FROM (
	SELECT 
		PatientICN, CAST(OutcomeDate AS DATE) AS OutcomeDate, relative_days, type
	FROM(
		SELECT
			#cohort_final.PatientICN, c.ServiceFromDate AS OutcomeDate, DATEDIFF(day, AppointmentRequestDate, c.ServiceFromDate) AS relative_days, 
			CASE 
				WHEN PitPlaceOfService='Emergency Room - Hospital' THEN 'COMM-ED' WHEN PitPlaceOfService='Urgent Care Facility' THEN 'COMM-UCC' ELSE 'COMM-HOSP'
			END AS type
		FROM CDWWork.PIT.PITClaim AS a
		INNER JOIN CDWWork.PIT.PitProfessionalClaim AS b
			ON a.PITClaimSID=b.PITClaimSID
		INNER JOIN CDWWork.PIT.PitProfessionalClaimDetails AS c
			ON b.PITClaimSID=c.PITCLaimSID
		INNER JOIN CDWWork.PIT.PitClaimDiagnosis AS d
			ON b.PITProfessionalClaimSID=d.PITProfessionalClaimSID
		INNER JOIN CDWWork.NDIM.PITDiagnosisCode AS dim
			ON d.PITDiagnosisCodeSID=dim.PITDiagnosisCodeSID
		INNER JOIN #PH_icd9
			ON dim.PITDiagnosisCode=#PH_icd9.ICD9Code
		INNER JOIN (SELECT PatientICN, PitPatientSID FROM CDWWork.SVeteran.PitPatient WHERE PitPatientSID>0 AND NOT PatientICN='*Unknown at this time*') AS pat
			ON c.PitPatientSID=pat.PitPatientSID
		INNER JOIN #cohort_final
			ON pat.PatientICN=#cohort_final.PatientICN
		INNER JOIN CDWWork.NDim.PITPlaceOfService AS pos
			ON b.PITPlaceOfServiceSID=pos.PITPlaceOfServiceSID
		WHERE a.CurrentFlag='Y' AND DATEDIFF(day, #cohort_final.AppointmentRequestDate, c.ServiceFromDate)>=(-365) AND DATEDIFF(day, #cohort_final.AppointmentRequestDate, c.ServiceFromDate)<=(365*3) AND NOT DATEDIFF(day, #cohort_final.AppointmentRequestDate, c.ServiceFromDate)=0
		AND PITPlaceOfService IN ('Inpatient Hospital', 'Urgent Care Facility', 'Emergency Room - Hospital', 'Inpatient Psychiatric Facility', 'Psychiatric Facility-Partial Hospitalization')--, 'Residential Substance Abuse Treatment Facility', 'Psychiatric Residential Treatment Center')
	) AS X
	UNION(
		SELECT
			#cohort_final.PatientICN, c.ServiceFromDate AS OutcomeDate, DATEDIFF(day, AppointmentRequestDate, c.ServiceFromDate) AS relative_days, 
			CASE 
				WHEN PitPlaceOfService='Emergency Room - Hospital' THEN 'COMM-ED' WHEN PitPlaceOfService='Urgent Care Facility' THEN 'COMM-UCC' ELSE 'COMM-HOSP'
			END AS type
		FROM CDWWork.PIT.PITClaim AS a
		INNER JOIN CDWWork.PIT.PitProfessionalClaim AS b
			ON a.PITClaimSID=b.PITClaimSID
		INNER JOIN CDWWork.PIT.PitProfessionalClaimDetails AS c
			ON b.PITClaimSID=c.PITCLaimSID
		INNER JOIN CDWWork.PIT.PitClaimDiagnosis AS d
			ON b.PITProfessionalClaimSID=d.PITProfessionalClaimSID
		INNER JOIN CDWWork.NDIM.PITDiagnosisCode AS dim
			ON d.PITDiagnosisCodeSID=dim.PITDiagnosisCodeSID
		INNER JOIN #PH_icd10
			ON dim.PITDiagnosisCode=#PH_icd10.ICD10Code
		INNER JOIN (SELECT PatientICN, PitPatientSID FROM CDWWork.SVeteran.PitPatient WHERE PitPatientSID>0 AND NOT PatientICN='*Unknown at this time*') AS pat
			ON c.PitPatientSID=pat.PitPatientSID
		INNER JOIN #cohort_final
			ON pat.PatientICN=#cohort_final.PatientICN
		INNER JOIN CDWWork.NDim.PITPlaceOfService AS pos
			ON b.PITPlaceOfServiceSID=pos.PITPlaceOfServiceSID
		WHERE a.CurrentFlag='Y' AND DATEDIFF(day, #cohort_final.AppointmentRequestDate, c.ServiceFromDate)>=(-365) AND DATEDIFF(day, #cohort_final.AppointmentRequestDate, c.ServiceFromDate)<=(365*3) AND NOT DATEDIFF(day, #cohort_final.AppointmentRequestDate, c.ServiceFromDate)=0
		AND PITPlaceOfService IN ('Inpatient Hospital', 'Urgent Care Facility', 'Emergency Room - Hospital', 'Inpatient Psychiatric Facility', 'Psychiatric Facility-Partial Hospitalization')--, 'Residential Substance Abuse Treatment Facility', 'Psychiatric Residential Treatment Center')
	)
	UNION(
		SELECT
			#cohort_final.PatientICN, c.ServiceDate AS OutcomeDate, DATEDIFF(day, AppointmentRequestDate, c.ServiceDate) AS relative_days, 
			CASE 
				WHEN PitPlaceOfService='Emergency Room - Hospital' THEN 'COMM-ED' WHEN PitPlaceOfService='Urgent Care Facility' THEN 'COMM-UCC' ELSE 'COMM-HOSP'
			END AS type
		FROM CDWWork.PIT.PITClaim AS a
		INNER JOIN CDWWork.PIT.PitInstitutionalClaim AS b
			ON a.PITClaimSID=b.PITClaimSID
		INNER JOIN CDWWork.PIT.PitInstitutionalClaimDetails AS c
			ON b.PITClaimSID=c.PITCLaimSID
		INNER JOIN CDWWork.PIT.PitClaimDiagnosis AS d
			ON b.PITInstitutionalClaimSID=d.PITInstitutionalClaimSID
		INNER JOIN CDWWork.NDIM.PITDiagnosisCode AS dim
			ON d.PITDiagnosisCodeSID=dim.PITDiagnosisCodeSID
		INNER JOIN #PH_icd9
			ON dim.PITDiagnosisCode=#PH_icd9.ICD9Code
		INNER JOIN (SELECT PatientICN, PitPatientSID FROM CDWWork.SVeteran.PitPatient WHERE PitPatientSID>0 AND NOT PatientICN='*Unknown at this time*') AS pat
			ON c.PitPatientSID=pat.PitPatientSID
		INNER JOIN #cohort_final
			ON pat.PatientICN=#cohort_final.PatientICN
		INNER JOIN CDWWork.NDim.PITPlaceOfService AS pos
			ON b.PITPlaceOfServiceSID=pos.PITPlaceOfServiceSID
		WHERE a.CurrentFlag='Y' AND DATEDIFF(day, #cohort_final.AppointmentRequestDate, c.ServiceDate)>=(-365) AND DATEDIFF(day, #cohort_final.AppointmentRequestDate, c.ServiceDate)<=(365*3) AND NOT DATEDIFF(day, #cohort_final.AppointmentRequestDate, c.ServiceDate)=0
		AND PITPlaceOfService IN ('Inpatient Hospital', 'Urgent Care Facility', 'Emergency Room - Hospital', 'Inpatient Psychiatric Facility', 'Psychiatric Facility-Partial Hospitalization')--, 'Residential Substance Abuse Treatment Facility', 'Psychiatric Residential Treatment Center')
	)
	UNION(
		SELECT
			#cohort_final.PatientICN, c.ServiceDate AS OutcomeDate, DATEDIFF(day, AppointmentRequestDate, c.ServiceDate) AS relative_days, 
			CASE 
				WHEN PitPlaceOfService='Emergency Room - Hospital' THEN 'COMM-ED' WHEN PitPlaceOfService='Urgent Care Facility' THEN 'COMM-UCC' ELSE 'COMM-HOSP'
			END AS type
		FROM CDWWork.PIT.PITClaim AS a
		INNER JOIN CDWWork.PIT.PitInstitutionalClaim AS b
			ON a.PITClaimSID=b.PITClaimSID
		INNER JOIN CDWWork.PIT.PitInstitutionalClaimDetails AS c
			ON b.PITClaimSID=c.PITCLaimSID
		INNER JOIN CDWWork.PIT.PitClaimDiagnosis AS d
			ON b.PITInstitutionalClaimSID=d.PITInstitutionalClaimSID
		INNER JOIN CDWWork.NDIM.PITDiagnosisCode AS dim
			ON d.PITDiagnosisCodeSID=dim.PITDiagnosisCodeSID
		INNER JOIN #PH_icd10
			ON dim.PITDiagnosisCode=#PH_icd10.ICD10Code
		INNER JOIN (SELECT PatientICN, PitPatientSID FROM CDWWork.SVeteran.PitPatient WHERE PitPatientSID>0 AND NOT PatientICN='*Unknown at this time*') AS pat
			ON c.PitPatientSID=pat.PitPatientSID
		INNER JOIN #cohort_final
			ON pat.PatientICN=#cohort_final.PatientICN
		INNER JOIN CDWWork.NDim.PITPlaceOfService AS pos
			ON b.PITPlaceOfServiceSID=pos.PITPlaceOfServiceSID
		WHERE a.CurrentFlag='Y' AND DATEDIFF(day, #cohort_final.AppointmentRequestDate, c.ServiceDate)>=(-365) AND DATEDIFF(day, #cohort_final.AppointmentRequestDate, c.ServiceDate)<=(365*3) AND NOT DATEDIFF(day, #cohort_final.AppointmentRequestDate, c.ServiceDate)=0
		AND PITPlaceOfService IN ('Inpatient Hospital', 'Urgent Care Facility', 'Emergency Room - Hospital', 'Inpatient Psychiatric Facility', 'Psychiatric Facility-Partial Hospitalization')--, 'Residential Substance Abuse Treatment Facility', 'Psychiatric Residential Treatment Center')
	)
	UNION(
		SELECT #cohort_final.PatientICN, CAST(InitialTreatmentDateTime AS date) AS OutcomeDate, DATEDIFF(day, AppointmentRequestDate, InitialTreatmentDateTime) AS relative_days,
		CASE WHEN IBPlaceOfService='EMERGENCY ROOM - HOSPITAL' THEN 'COMM-ED' WHEN IBPlaceOfService='URGENT CARE FACILITY' THEN 'COMM-UCC' ELSE 'COMM-HOSP' END AS type
		FROM CDWWork.Fee.FeeServiceProvided AS a
		INNER JOIN CDWWork.Fee.FeeInitialTreatment AS b
			ON a.FeeInitialTreatmentSID=b.FeeInitialTreatmentSID
		INNER JOIN #PH_icd9
				ON a.ICD9SID=#PH_icd9.ICD9SID
		INNER JOIN (SELECT PatientICN, PatientSID FROM CDWWork.Patient.Patient WHERE PatientSID>0 AND NOT PatientICN='*Unknown at this time*') AS pat
			ON a.PatientSID=pat.PatientSID
		INNER JOIN #cohort_final
			ON pat.PatientICN=#cohort_final.PatientICN
		INNER JOIN CDWWork.Dim.IBPlaceOfService AS pos
			ON a.IBPlaceOfServiceSID=pos.IBPlaceOfServiceSID
		WHERE DATEDIFF(day, #cohort_final.AppointmentRequestDate, InitialTreatmentDateTime)>=(-365) AND DATEDIFF(day, #cohort_final.AppointmentRequestDate, InitialTreatmentDateTime)<=(365*3) AND NOT DATEDIFF(day, #cohort_final.AppointmentRequestDate, InitialTreatmentDateTime)=0
		AND IBPlaceOfService IN ('EMERGENCY ROOM - HOSPITAL', 'URGENT CARE FACILITY', 'Hospital as Inpatient', 'INPATIENT HOSPITAL', 'INPATIENT PSYCHIATRIC FACILITY', 'PSY. FAC. PARTIAL HOSPITALIZATION')--, 'PSYCHIATRIC RESIDENTIAL TREATMENT CENTER', 'Resdential Substance Abuse Facility', 'RESIDENTIAL SUBSTANCE ABUSE TREATMENT FACILITY', 'BCBS RES. SUBSTANCE ABUSE TREATMENT FACILITY')
	)
	UNION(
		SELECT #cohort_final.PatientICN, CAST(InitialTreatmentDateTime AS date) AS OutcomeDate, DATEDIFF(day, AppointmentRequestDate, InitialTreatmentDateTime) AS relative_days,
		CASE WHEN IBPlaceOfService='EMERGENCY ROOM - HOSPITAL' THEN 'COMM-ED' WHEN IBPlaceOfService='URGENT CARE FACILITY' THEN 'COMM-UCC' ELSE 'COMM-HOSP' END AS type
		FROM CDWWork.Fee.FeeServiceProvided AS a
		INNER JOIN CDWWork.Fee.FeeInitialTreatment AS b
			ON a.FeeInitialTreatmentSID=b.FeeInitialTreatmentSID
		INNER JOIN #PH_icd10
				ON a.ICD10SID=#PH_icd10.ICD10SID
		INNER JOIN (SELECT PatientICN, PatientSID FROM CDWWork.Patient.Patient WHERE PatientSID>0 AND NOT PatientICN='*Unknown at this time*') AS pat
			ON a.PatientSID=pat.PatientSID
		INNER JOIN #cohort_final
			ON pat.PatientICN=#cohort_final.PatientICN
		INNER JOIN CDWWork.Dim.IBPlaceOfService AS pos
			ON a.IBPlaceOfServiceSID=pos.IBPlaceOfServiceSID
		WHERE DATEDIFF(day, #cohort_final.AppointmentRequestDate, InitialTreatmentDateTime)>=(-365) AND DATEDIFF(day, #cohort_final.AppointmentRequestDate, InitialTreatmentDateTime)<=(365*3) AND NOT DATEDIFF(day, #cohort_final.AppointmentRequestDate, InitialTreatmentDateTime)=0
		AND IBPlaceOfService IN ('EMERGENCY ROOM - HOSPITAL', 'URGENT CARE FACILITY', 'Hospital as Inpatient', 'INPATIENT HOSPITAL', 'INPATIENT PSYCHIATRIC FACILITY', 'PSY. FAC. PARTIAL HOSPITALIZATION')--, 'PSYCHIATRIC RESIDENTIAL TREATMENT CENTER', 'Resdential Substance Abuse Facility', 'RESIDENTIAL SUBSTANCE ABUSE TREATMENT FACILITY', 'BCBS RES. SUBSTANCE ABUSE TREATMENT FACILITY')

	)
) AS Z;

-- CMS
SELECT DISTINCT
	PatientICN, OutcomeDate, relative_days,
	CASE WHEN type='HOSP' THEN 'CMS-HOSP' WHEN type='ED' THEN 'CMS-ED' WHEN type='UCC' THEN 'CMS-UCC' END AS type
INTO #ph_cms
FROM (
	SELECT 
		PatientICN, CAST(VisitDateTime AS DATE) AS OutcomeDate, relative_days, type
	FROM(
		SELECT #cohort_final.PatientICN, cms.VisitDateTime, DATEDIFF(day, #cohort_final.AppointmentRequestDate, cms.VisitDateTime) AS relative_days, type
		FROM OMHO_QFR.ECON.smc_pcpvalueadd AS cms
		INNER JOIN #cohort_final
			ON #cohort_final.PatientICN=cms.PatientICN
		INNER JOIN #PH_icd9
			ON cms.Dx1=#PH_icd9.ICD9Code OR cms.Dx2=#PH_icd9.ICD9Code OR cms.Dx3=#PH_icd9.ICD9Code
		WHERE DATEDIFF(day, #cohort_final.AppointmentRequestDate, cms.VisitDateTime)>=(-365) AND DATEDIFF(day, #cohort_final.AppointmentRequestDate, cms.VisitDateTime)<=(365*3) AND NOT DATEDIFF(day, #cohort_final.AppointmentRequestDate, cms.VisitDateTime)=0
		AND type IN ('HOSP', 'ED', 'UCC')
	) AS a
	UNION(
		SELECT #cohort_final.PatientICN, cms.VisitDateTime, DATEDIFF(day, #cohort_final.AppointmentRequestDate, cms.VisitDateTime) AS relative_days, type
		FROM OMHO_QFR.ECON.smc_pcpvalueadd AS cms
		INNER JOIN #cohort_final
			ON #cohort_final.PatientICN=cms.PatientICN
		INNER JOIN #PH_icd10
			ON cms.Dx1=REPLACE(#PH_icd10.ICD10Code, '.', '') OR cms.Dx2=REPLACE(#PH_icd10.ICD10Code, '.', '') OR cms.Dx3=REPLACE(#PH_icd10.ICD10Code, '.', '')
		WHERE DATEDIFF(day, #cohort_final.AppointmentRequestDate, cms.VisitDateTime)>=(-365) AND DATEDIFF(day, #cohort_final.AppointmentRequestDate, cms.VisitDateTime)<=(365*3) AND NOT DATEDIFF(day, #cohort_final.AppointmentRequestDate, cms.VisitDateTime)=0
		AND type IN ('HOSP', 'ED', 'UCC')
	)
) AS z;

SELECT DISTINCT
	PatientICN, OutcomeDate, relative_days,
	CASE WHEN type='HOSP' THEN 'CMS-HOSP' WHEN type='ED' THEN 'CMS-ED' WHEN type='UCC' THEN 'CMS-UCC' END AS type
INTO #mh_cms
FROM (
	SELECT 
		PatientICN, CAST(VisitDateTime AS DATE) AS OutcomeDate, relative_days, type
	FROM(
		SELECT #cohort_final.PatientICN, cms.VisitDateTime, DATEDIFF(day, #cohort_final.AppointmentRequestDate, cms.VisitDateTime) AS relative_days, type
		FROM OMHO_QFR.ECON.smc_pcpvalueadd AS cms
		INNER JOIN #cohort_final
			ON #cohort_final.PatientICN=cms.PatientICN
		INNER JOIN #MH_icd9
			ON cms.Dx1=#MH_icd9.ICD9Code OR cms.Dx2=#MH_icd9.ICD9Code OR cms.Dx3=#MH_icd9.ICD9Code
		WHERE DATEDIFF(day, #cohort_final.AppointmentRequestDate, cms.VisitDateTime)>=(-365) AND DATEDIFF(day, #cohort_final.AppointmentRequestDate, cms.VisitDateTime)<=(365*3) AND NOT DATEDIFF(day, #cohort_final.AppointmentRequestDate, cms.VisitDateTime)=0
		AND type IN ('HOSP', 'ED', 'UCC')
	) AS a
	UNION(
		SELECT #cohort_final.PatientICN, cms.VisitDateTime, DATEDIFF(day, #cohort_final.AppointmentRequestDate, cms.VisitDateTime) AS relative_days, type
		FROM OMHO_QFR.ECON.smc_pcpvalueadd AS cms
		INNER JOIN #cohort_final
			ON #cohort_final.PatientICN=cms.PatientICN
		INNER JOIN #MH_icd10
			ON cms.Dx1=REPLACE(#MH_icd10.ICD10Code, '.', '') OR cms.Dx2=REPLACE(#MH_icd10.ICD10Code, '.', '') OR cms.Dx3=REPLACE(#MH_icd10.ICD10Code, '.', '')
		WHERE DATEDIFF(day, #cohort_final.AppointmentRequestDate, cms.VisitDateTime)>=(-365) AND DATEDIFF(day, #cohort_final.AppointmentRequestDate, cms.VisitDateTime)<=(365*3) AND NOT DATEDIFF(day, #cohort_final.AppointmentRequestDate, cms.VisitDateTime)=0
		AND type IN ('HOSP', 'ED', 'UCC')
	)
) AS z;


SELECT
	PatientICN,
	-- VA HOSP
	MAX(CASE WHEN relative_days<0 AND type='HOSP' THEN 1 ELSE 0 END) AS MH_dummy_HOSP_lag1, SUM(CASE WHEN relative_days<0 AND type='HOSP' THEN 1 ELSE 0 END) AS MH_count_HOSP_lag1,
	MAX(CASE WHEN relative_days>0 AND type='HOSP' AND relative_days<=365 THEN 1 ELSE 0 END) AS MH_dummy_HOSP_yr1, SUM(CASE WHEN relative_days>0 AND type='HOSP' AND relative_days<=365 THEN 1 ELSE 0 END) AS MH_count_HOSP_yr1,
	MAX(CASE WHEN relative_days>0 AND type='HOSP' AND relative_days<=(365*2) THEN 1 ELSE 0 END) AS MH_dummy_HOSP_cum2, SUM(CASE WHEN relative_days>0 AND type='HOSP' AND relative_days<=(365*2) THEN 1 ELSE 0 END) AS MH_count_HOSP_cum2,
	MAX(CASE WHEN relative_days>0 AND type='HOSP' THEN 1 ELSE 0 END) AS MH_dummy_HOSP_cum3, SUM(CASE WHEN relative_days>0 AND type='HOSP' THEN 1 ELSE 0 END) AS MH_count_HOSP_cum3,
	-- VA ED
	MAX(CASE WHEN relative_days<0 AND type='ED' THEN 1 ELSE 0 END) AS MH_dummy_ED_lag1, SUM(CASE WHEN relative_days<0 AND type='ED' THEN 1 ELSE 0 END) AS MH_count_ED_lag1,
	MAX(CASE WHEN relative_days>0 AND type='ED' AND relative_days<=365 THEN 1 ELSE 0 END) AS MH_dummy_ED_yr1, SUM(CASE WHEN relative_days>0 AND type='ED' AND relative_days<=365 THEN 1 ELSE 0 END) AS MH_count_ED_yr1,
	MAX(CASE WHEN relative_days>0 AND type='ED' AND relative_days<=(365*2) THEN 1 ELSE 0 END) AS MH_dummy_ED_cum2, SUM(CASE WHEN relative_days>0 AND type='ED' AND relative_days<=(365*2) THEN 1 ELSE 0 END) AS MH_count_ED_cum2,
	MAX(CASE WHEN relative_days>0 AND type='ED' THEN 1 ELSE 0 END) AS MH_dummy_ED_cum3, SUM(CASE WHEN relative_days>0 AND type='ED' THEN 1 ELSE 0 END) AS MH_count_ED_cum3,
	-- VA UCC
	MAX(CASE WHEN relative_days<0 AND type='UCC' THEN 1 ELSE 0 END) AS MH_dummy_UCC_lag1, SUM(CASE WHEN relative_days<0 AND type='UCC' THEN 1 ELSE 0 END) AS MH_count_UCC_lag1,
	MAX(CASE WHEN relative_days>0 AND type='UCC' AND relative_days<=365 THEN 1 ELSE 0 END) AS MH_dummy_UCC_yr1, SUM(CASE WHEN relative_days>0 AND type='UCC' AND relative_days<=365 THEN 1 ELSE 0 END) AS MH_count_UCC_yr1,
	MAX(CASE WHEN relative_days>0 AND type='UCC' AND relative_days<=(365*2) THEN 1 ELSE 0 END) AS MH_dummy_UCC_cum2, SUM(CASE WHEN relative_days>0 AND type='UCC' AND relative_days<=(365*2) THEN 1 ELSE 0 END) AS MH_count_UCC_cum2,
	MAX(CASE WHEN relative_days>0 AND type='UCC' THEN 1 ELSE 0 END) AS MH_dummy_UCC_cum3, SUM(CASE WHEN relative_days>0 AND type='UCC' THEN 1 ELSE 0 END) AS MH_count_UCC_cum3,
	-- COMM HOSP
	MAX(CASE WHEN relative_days<0 AND type='COMM-HOSP' THEN 1 ELSE 0 END) AS MH_dummy_COMMHOSP_lag1, SUM(CASE WHEN relative_days<0 AND type='COMM-HOSP' THEN 1 ELSE 0 END) AS MH_count_COMMHOSP_lag1,
	MAX(CASE WHEN relative_days>0 AND type='COMM-HOSP' AND relative_days<=365 THEN 1 ELSE 0 END) AS MH_dummy_COMMHOSP_yr1, SUM(CASE WHEN relative_days>0 AND type='COMM-HOSP' AND relative_days<=365 THEN 1 ELSE 0 END) AS MH_count_COMMHOSP_yr1,
	MAX(CASE WHEN relative_days>0 AND type='COMM-HOSP' AND relative_days<=(365*2) THEN 1 ELSE 0 END) AS MH_dummy_COMMHOSP_cum2, SUM(CASE WHEN relative_days>0 AND type='COMM-HOSP' AND relative_days<=(365*2) THEN 1 ELSE 0 END) AS MH_count_COMMHOSP_cum2,
	MAX(CASE WHEN relative_days>0 AND type='COMM-HOSP' THEN 1 ELSE 0 END) AS MH_dummy_COMMHOSP_cum3, SUM(CASE WHEN relative_days>0 AND type='COMM-HOSP' THEN 1 ELSE 0 END) AS MH_count_COMMHOSP_cum3,
	-- COMM ED
	MAX(CASE WHEN relative_days<0 AND type='COMM-ED' THEN 1 ELSE 0 END) AS MH_dummy_COMMED_lag1, SUM(CASE WHEN relative_days<0 AND type='COMM-ED' THEN 1 ELSE 0 END) AS MH_count_COMMED_lag1,
	MAX(CASE WHEN relative_days>0 AND type='COMM-ED' AND relative_days<=365 THEN 1 ELSE 0 END) AS MH_dummy_COMMED_yr1, SUM(CASE WHEN relative_days>0 AND type='COMM-ED' AND relative_days<=365 THEN 1 ELSE 0 END) AS MH_count_COMMED_yr1,
	MAX(CASE WHEN relative_days>0 AND type='COMM-ED' AND relative_days<=(365*2) THEN 1 ELSE 0 END) AS MH_dummy_COMMED_cum2, SUM(CASE WHEN relative_days>0 AND type='COMM-ED' AND relative_days<=(365*2) THEN 1 ELSE 0 END) AS MH_count_COMMED_cum2,
	MAX(CASE WHEN relative_days>0 AND type='COMM-ED' THEN 1 ELSE 0 END) AS MH_dummy_COMMED_cum3, SUM(CASE WHEN relative_days>0 AND type='COMM-ED' THEN 1 ELSE 0 END) AS MH_count_COMMED_cum3,
	-- COMM UCC
	MAX(CASE WHEN relative_days<0 AND type='COMM-UCC' THEN 1 ELSE 0 END) AS MH_dummy_COMMUCC_lag1, SUM(CASE WHEN relative_days<0 AND type='COMM-UCC' THEN 1 ELSE 0 END) AS MH_count_COMMUCC_lag1,
	MAX(CASE WHEN relative_days>0 AND type='COMM-UCC' AND relative_days<=365 THEN 1 ELSE 0 END) AS MH_dummy_COMMUCC_yr1, SUM(CASE WHEN relative_days>0 AND type='COMM-UCC' AND relative_days<=365 THEN 1 ELSE 0 END) AS MH_count_COMMUCC_yr1,
	MAX(CASE WHEN relative_days>0 AND type='COMM-UCC' AND relative_days<=(365*2) THEN 1 ELSE 0 END) AS MH_dummy_COMMUCC_cum2, SUM(CASE WHEN relative_days>0 AND type='COMM-UCC' AND relative_days<=(365*2) THEN 1 ELSE 0 END) AS MH_count_COMMUCC_cum2,
	MAX(CASE WHEN relative_days>0 AND type='COMM-UCC' THEN 1 ELSE 0 END) AS MH_dummy_COMMUCC_cum3, SUM(CASE WHEN relative_days>0 AND type='COMM-UCC' THEN 1 ELSE 0 END) AS MH_count_COMMUCC_cum3,
	-- CMS HOSP
	MAX(CASE WHEN relative_days<0 AND type='CMS-HOSP' THEN 1 ELSE 0 END) AS MH_dummy_CMSHOSP_lag1, SUM(CASE WHEN relative_days<0 AND type='CMS-HOSP' THEN 1 ELSE 0 END) AS MH_count_CMSHOSP_lag1,
	MAX(CASE WHEN relative_days>0 AND type='CMS-HOSP' AND relative_days<=365 THEN 1 ELSE 0 END) AS MH_dummy_CMSHOSP_yr1, SUM(CASE WHEN relative_days>0 AND type='CMS-HOSP' AND relative_days<=365 THEN 1 ELSE 0 END) AS MH_count_CMSHOSP_yr1,
	MAX(CASE WHEN relative_days>0 AND type='CMS-HOSP' AND relative_days<=(365*2) THEN 1 ELSE 0 END) AS MH_dummy_CMSHOSP_cum2, SUM(CASE WHEN relative_days>0 AND type='CMS-HOSP' AND relative_days<=(365*2) THEN 1 ELSE 0 END) AS MH_count_CMSHOSP_cum2,
	MAX(CASE WHEN relative_days>0 AND type='CMS-HOSP' THEN 1 ELSE 0 END) AS MH_dummy_CMSHOSP_cum3, SUM(CASE WHEN relative_days>0 AND type='CMS-HOSP' THEN 1 ELSE 0 END) AS MH_count_CMSHOSP_cum3,
	-- CMS ED
	MAX(CASE WHEN relative_days<0 AND type='CMS-ED' THEN 1 ELSE 0 END) AS MH_dummy_CMSED_lag1, SUM(CASE WHEN relative_days<0 AND type='CMS-ED' THEN 1 ELSE 0 END) AS MH_count_CMSED_lag1,
	MAX(CASE WHEN relative_days>0 AND type='CMS-ED' AND relative_days<=365 THEN 1 ELSE 0 END) AS MH_dummy_CMSED_yr1, SUM(CASE WHEN relative_days>0 AND type='CMS-ED' AND relative_days<=365 THEN 1 ELSE 0 END) AS MH_count_CMSED_yr1,
	MAX(CASE WHEN relative_days>0 AND type='CMS-ED' AND relative_days<=(365*2) THEN 1 ELSE 0 END) AS MH_dummy_CMSED_cum2, SUM(CASE WHEN relative_days>0 AND type='CMS-ED' AND relative_days<=(365*2) THEN 1 ELSE 0 END) AS MH_count_CMSED_cum2,
	MAX(CASE WHEN relative_days>0 AND type='CMS-ED' THEN 1 ELSE 0 END) AS MH_dummy_CMSED_cum3, SUM(CASE WHEN relative_days>0 AND type='CMS-ED' THEN 1 ELSE 0 END) AS MH_count_CMSED_cum3,
	-- CMS UCC
	MAX(CASE WHEN relative_days<0 AND type='CMS-UCC' THEN 1 ELSE 0 END) AS MH_dummy_CMSUCC_lag1, SUM(CASE WHEN relative_days<0 AND type='CMS-UCC' THEN 1 ELSE 0 END) AS MH_count_CMSUCC_lag1,
	MAX(CASE WHEN relative_days>0 AND type='CMS-UCC' AND relative_days<=365 THEN 1 ELSE 0 END) AS MH_dummy_CMSUCC_yr1, SUM(CASE WHEN relative_days>0 AND type='CMS-UCC' AND relative_days<=365 THEN 1 ELSE 0 END) AS MH_count_CMSUCC_yr1,
	MAX(CASE WHEN relative_days>0 AND type='CMS-UCC' AND relative_days<=(365*2) THEN 1 ELSE 0 END) AS MH_dummy_CMSUCC_cum2, SUM(CASE WHEN relative_days>0 AND type='CMS-UCC' AND relative_days<=(365*2) THEN 1 ELSE 0 END) AS MH_count_CMSUCC_cum2,
	MAX(CASE WHEN relative_days>0 AND type='CMS-UCC' THEN 1 ELSE 0 END) AS MH_dummy_CMSUCC_cum3, SUM(CASE WHEN relative_days>0 AND type='CMS-UCC' THEN 1 ELSE 0 END) AS MH_count_CMSUCC_cum3
	
INTO #mh_agg
FROM(
	SELECT DISTINCT PatientICN, OutcomeDate, relative_days, type FROM (
		SELECT PatientICN, OutcomeDate, relative_days, type FROM #mh_inpat UNION (SELECT PatientICN, OutcomeDate, relative_days, type FROM #mh_ed) UNION (SELECT PatientICN, OutcomeDate, relative_days, type FROM #mh_pit) UNION (SELECT PatientICN, OutcomeDate, relative_days, type FROM #mh_cms)
	) AS z
) AS zz
GROUP BY PatientICN

SELECT
	PatientICN,
	-- VA HOSP
	MAX(CASE WHEN relative_days<0 AND type='HOSP' THEN 1 ELSE 0 END) AS PH_dummy_HOSP_lag1, SUM(CASE WHEN relative_days<0 AND type='HOSP' THEN 1 ELSE 0 END) AS PH_count_HOSP_lag1,
	MAX(CASE WHEN relative_days>0 AND type='HOSP' AND relative_days<=365 THEN 1 ELSE 0 END) AS PH_dummy_HOSP_yr1, SUM(CASE WHEN relative_days>0 AND type='HOSP' AND relative_days<=365 THEN 1 ELSE 0 END) AS PH_count_HOSP_yr1,
	MAX(CASE WHEN relative_days>0 AND type='HOSP' AND relative_days<=(365*2) THEN 1 ELSE 0 END) AS PH_dummy_HOSP_cum2, SUM(CASE WHEN relative_days>0 AND type='HOSP' AND relative_days<=(365*2) THEN 1 ELSE 0 END) AS PH_count_HOSP_cum2,
	MAX(CASE WHEN relative_days>0 AND type='HOSP' THEN 1 ELSE 0 END) AS PH_dummy_HOSP_cum3, SUM(CASE WHEN relative_days>0 AND type='HOSP' THEN 1 ELSE 0 END) AS PH_count_HOSP_cum3,
	-- VA ED
	MAX(CASE WHEN relative_days<0 AND type='ED' THEN 1 ELSE 0 END) AS PH_dummy_ED_lag1, SUM(CASE WHEN relative_days<0 AND type='ED' THEN 1 ELSE 0 END) AS PH_count_ED_lag1,
	MAX(CASE WHEN relative_days>0 AND type='ED' AND relative_days<=365 THEN 1 ELSE 0 END) AS PH_dummy_ED_yr1, SUM(CASE WHEN relative_days>0 AND type='ED' AND relative_days<=365 THEN 1 ELSE 0 END) AS PH_count_ED_yr1,
	MAX(CASE WHEN relative_days>0 AND type='ED' AND relative_days<=(365*2) THEN 1 ELSE 0 END) AS PH_dummy_ED_cum2, SUM(CASE WHEN relative_days>0 AND type='ED' AND relative_days<=(365*2) THEN 1 ELSE 0 END) AS PH_count_ED_cum2,
	MAX(CASE WHEN relative_days>0 AND type='ED' THEN 1 ELSE 0 END) AS PH_dummy_ED_cum3, SUM(CASE WHEN relative_days>0 AND type='ED' THEN 1 ELSE 0 END) AS PH_count_ED_cum3,
	-- VA UCC
	MAX(CASE WHEN relative_days<0 AND type='UCC' THEN 1 ELSE 0 END) AS PH_dummy_UCC_lag1, SUM(CASE WHEN relative_days<0 AND type='UCC' THEN 1 ELSE 0 END) AS PH_count_UCC_lag1,
	MAX(CASE WHEN relative_days>0 AND type='UCC' AND relative_days<=365 THEN 1 ELSE 0 END) AS PH_dummy_UCC_yr1, SUM(CASE WHEN relative_days>0 AND type='UCC' AND relative_days<=365 THEN 1 ELSE 0 END) AS PH_count_UCC_yr1,
	MAX(CASE WHEN relative_days>0 AND type='UCC' AND relative_days<=(365*2) THEN 1 ELSE 0 END) AS PH_dummy_UCC_cum2, SUM(CASE WHEN relative_days>0 AND type='UCC' AND relative_days<=(365*2) THEN 1 ELSE 0 END) AS PH_count_UCC_cum2,
	MAX(CASE WHEN relative_days>0 AND type='UCC' THEN 1 ELSE 0 END) AS PH_dummy_UCC_cum3, SUM(CASE WHEN relative_days>0 AND type='UCC' THEN 1 ELSE 0 END) AS PH_count_UCC_cum3,
	-- COMM HOSP
	MAX(CASE WHEN relative_days<0 AND type='COMM-HOSP' THEN 1 ELSE 0 END) AS PH_dummy_COMMHOSP_lag1, SUM(CASE WHEN relative_days<0 AND type='COMM-HOSP' THEN 1 ELSE 0 END) AS PH_count_COMMHOSP_lag1,
	MAX(CASE WHEN relative_days>0 AND type='COMM-HOSP' AND relative_days<=365 THEN 1 ELSE 0 END) AS PH_dummy_COMMHOSP_yr1, SUM(CASE WHEN relative_days>0 AND type='COMM-HOSP' AND relative_days<=365 THEN 1 ELSE 0 END) AS PH_count_COMMHOSP_yr1,
	MAX(CASE WHEN relative_days>0 AND type='COMM-HOSP' AND relative_days<=(365*2) THEN 1 ELSE 0 END) AS PH_dummy_COMMHOSP_cum2, SUM(CASE WHEN relative_days>0 AND type='COMM-HOSP' AND relative_days<=(365*2) THEN 1 ELSE 0 END) AS PH_count_COMMHOSP_cum2,
	MAX(CASE WHEN relative_days>0 AND type='COMM-HOSP' THEN 1 ELSE 0 END) AS PH_dummy_COMMHOSP_cum3, SUM(CASE WHEN relative_days>0 AND type='COMM-HOSP' THEN 1 ELSE 0 END) AS PH_count_COMMHOSP_cum3,
	-- COMM ED
	MAX(CASE WHEN relative_days<0 AND type='COMM-ED' THEN 1 ELSE 0 END) AS PH_dummy_COMMED_lag1, SUM(CASE WHEN relative_days<0 AND type='COMM-ED' THEN 1 ELSE 0 END) AS PH_count_COMMED_lag1,
	MAX(CASE WHEN relative_days>0 AND type='COMM-ED' AND relative_days<=365 THEN 1 ELSE 0 END) AS PH_dummy_COMMED_yr1, SUM(CASE WHEN relative_days>0 AND type='COMM-ED' AND relative_days<=365 THEN 1 ELSE 0 END) AS PH_count_COMMED_yr1,
	MAX(CASE WHEN relative_days>0 AND type='COMM-ED' AND relative_days<=(365*2) THEN 1 ELSE 0 END) AS PH_dummy_COMMED_cum2, SUM(CASE WHEN relative_days>0 AND type='COMM-ED' AND relative_days<=(365*2) THEN 1 ELSE 0 END) AS PH_count_COMMED_cum2,
	MAX(CASE WHEN relative_days>0 AND type='COMM-ED' THEN 1 ELSE 0 END) AS PH_dummy_COMMED_cum3, SUM(CASE WHEN relative_days>0 AND type='COMM-ED' THEN 1 ELSE 0 END) AS PH_count_COMMED_cum3,
	-- COMM UCC
	MAX(CASE WHEN relative_days<0 AND type='COMM-UCC' THEN 1 ELSE 0 END) AS PH_dummy_COMMUCC_lag1, SUM(CASE WHEN relative_days<0 AND type='COMM-UCC' THEN 1 ELSE 0 END) AS PH_count_COMMUCC_lag1,
	MAX(CASE WHEN relative_days>0 AND type='COMM-UCC' AND relative_days<=365 THEN 1 ELSE 0 END) AS PH_dummy_COMMUCC_yr1, SUM(CASE WHEN relative_days>0 AND type='COMM-UCC' AND relative_days<=365 THEN 1 ELSE 0 END) AS PH_count_COMMUCC_yr1,
	MAX(CASE WHEN relative_days>0 AND type='COMM-UCC' AND relative_days<=(365*2) THEN 1 ELSE 0 END) AS PH_dummy_COMMUCC_cum2, SUM(CASE WHEN relative_days>0 AND type='COMM-UCC' AND relative_days<=(365*2) THEN 1 ELSE 0 END) AS PH_count_COMMUCC_cum2,
	MAX(CASE WHEN relative_days>0 AND type='COMM-UCC' THEN 1 ELSE 0 END) AS PH_dummy_COMMUCC_cum3, SUM(CASE WHEN relative_days>0 AND type='COMM-UCC' THEN 1 ELSE 0 END) AS PH_count_COMMUCC_cum3,
	-- CMS HOSP
	MAX(CASE WHEN relative_days<0 AND type='CMS-HOSP' THEN 1 ELSE 0 END) AS PH_dummy_CMSHOSP_lag1, SUM(CASE WHEN relative_days<0 AND type='CMS-HOSP' THEN 1 ELSE 0 END) AS PH_count_CMSHOSP_lag1,
	MAX(CASE WHEN relative_days>0 AND type='CMS-HOSP' AND relative_days<=365 THEN 1 ELSE 0 END) AS PH_dummy_CMSHOSP_yr1, SUM(CASE WHEN relative_days>0 AND type='CMS-HOSP' AND relative_days<=365 THEN 1 ELSE 0 END) AS PH_count_CMSHOSP_yr1,
	MAX(CASE WHEN relative_days>0 AND type='CMS-HOSP' AND relative_days<=(365*2) THEN 1 ELSE 0 END) AS PH_dummy_CMSHOSP_cum2, SUM(CASE WHEN relative_days>0 AND type='CMS-HOSP' AND relative_days<=(365*2) THEN 1 ELSE 0 END) AS PH_count_CMSHOSP_cum2,
	MAX(CASE WHEN relative_days>0 AND type='CMS-HOSP' THEN 1 ELSE 0 END) AS PH_dummy_CMSHOSP_cum3, SUM(CASE WHEN relative_days>0 AND type='CMS-HOSP' THEN 1 ELSE 0 END) AS PH_count_CMSHOSP_cum3,
	-- CMS ED
	MAX(CASE WHEN relative_days<0 AND type='CMS-ED' THEN 1 ELSE 0 END) AS PH_dummy_CMSED_lag1, SUM(CASE WHEN relative_days<0 AND type='CMS-ED' THEN 1 ELSE 0 END) AS PH_count_CMSED_lag1,
	MAX(CASE WHEN relative_days>0 AND type='CMS-ED' AND relative_days<=365 THEN 1 ELSE 0 END) AS PH_dummy_CMSED_yr1, SUM(CASE WHEN relative_days>0 AND type='CMS-ED' AND relative_days<=365 THEN 1 ELSE 0 END) AS PH_count_CMSED_yr1,
	MAX(CASE WHEN relative_days>0 AND type='CMS-ED' AND relative_days<=(365*2) THEN 1 ELSE 0 END) AS PH_dummy_CMSED_cum2, SUM(CASE WHEN relative_days>0 AND type='CMS-ED' AND relative_days<=(365*2) THEN 1 ELSE 0 END) AS PH_count_CMSED_cum2,
	MAX(CASE WHEN relative_days>0 AND type='CMS-ED' THEN 1 ELSE 0 END) AS PH_dummy_CMSED_cum3, SUM(CASE WHEN relative_days>0 AND type='CMS-ED' THEN 1 ELSE 0 END) AS PH_count_CMSED_cum3,
	-- CMS UCC
	MAX(CASE WHEN relative_days<0 AND type='CMS-UCC' THEN 1 ELSE 0 END) AS PH_dummy_CMSUCC_lag1, SUM(CASE WHEN relative_days<0 AND type='CMS-UCC' THEN 1 ELSE 0 END) AS PH_count_CMSUCC_lag1,
	MAX(CASE WHEN relative_days>0 AND type='CMS-UCC' AND relative_days<=365 THEN 1 ELSE 0 END) AS PH_dummy_CMSUCC_yr1, SUM(CASE WHEN relative_days>0 AND type='CMS-UCC' AND relative_days<=365 THEN 1 ELSE 0 END) AS PH_count_CMSUCC_yr1,
	MAX(CASE WHEN relative_days>0 AND type='CMS-UCC' AND relative_days<=(365*2) THEN 1 ELSE 0 END) AS PH_dummy_CMSUCC_cum2, SUM(CASE WHEN relative_days>0 AND type='CMS-UCC' AND relative_days<=(365*2) THEN 1 ELSE 0 END) AS PH_count_CMSUCC_cum2,
	MAX(CASE WHEN relative_days>0 AND type='CMS-UCC' THEN 1 ELSE 0 END) AS PH_dummy_CMSUCC_cum3, SUM(CASE WHEN relative_days>0 AND type='CMS-UCC' THEN 1 ELSE 0 END) AS PH_count_CMSUCC_cum3
	
INTO #ph_agg
FROM(
	SELECT DISTINCT PatientICN, OutcomeDate, relative_days, type FROM (
		SELECT PatientICN, OutcomeDate, relative_days, type FROM #ph_inpat UNION (SELECT PatientICN, OutcomeDate, relative_days, type FROM #ph_ed) UNION (SELECT PatientICN, OutcomeDate, relative_days, type FROM #ph_pit) UNION (SELECT PatientICN, OutcomeDate, relative_days, type FROM #ph_cms)
	) AS z
) AS zz
GROUP BY PatientICN

SELECT CASE WHEN #mh_agg.PatientICN IS NOT NULL THEN #mh_agg.PatientICN ELSE #ph_agg.PatientICN END AS PatientICN, 
	MH_dummy_HOSP_lag1, MH_count_HOSP_lag1, MH_dummy_HOSP_yr1, MH_count_HOSP_yr1, MH_dummy_HOSP_cum2, MH_count_HOSP_cum2, MH_dummy_HOSP_cum3, MH_count_HOSP_cum3, PH_dummy_HOSP_lag1, PH_count_HOSP_lag1, PH_dummy_HOSP_yr1, PH_count_HOSP_yr1, PH_dummy_HOSP_cum2, PH_count_HOSP_cum2, PH_dummy_HOSP_cum3, PH_count_HOSP_cum3,
	MH_dummy_ED_lag1, MH_count_ED_lag1, MH_dummy_ED_yr1, MH_count_ED_yr1, MH_dummy_ED_cum2, MH_count_ED_cum2, MH_dummy_ED_cum3, MH_count_ED_cum3, PH_dummy_ED_lag1, PH_count_ED_lag1, PH_dummy_ED_yr1, PH_count_ED_yr1, PH_dummy_ED_cum2, PH_count_ED_cum2, PH_dummy_ED_cum3, PH_count_ED_cum3,
	MH_dummy_UCC_lag1, MH_count_UCC_lag1, MH_dummy_UCC_yr1, MH_count_UCC_yr1, MH_dummy_UCC_cum2, MH_count_UCC_cum2, MH_dummy_UCC_cum3, MH_count_UCC_cum3, PH_dummy_UCC_lag1, PH_count_UCC_lag1, PH_dummy_UCC_yr1, PH_count_UCC_yr1, PH_dummy_UCC_cum2, PH_count_UCC_cum2, PH_dummy_UCC_cum3, PH_count_UCC_cum3,
	MH_dummy_COMMHOSP_lag1, MH_count_COMMHOSP_lag1, MH_dummy_COMMHOSP_yr1, MH_count_COMMHOSP_yr1, MH_dummy_COMMHOSP_cum2, MH_count_COMMHOSP_cum2, MH_dummy_COMMHOSP_cum3, MH_count_COMMHOSP_cum3, PH_dummy_COMMHOSP_lag1, PH_count_COMMHOSP_lag1, PH_dummy_COMMHOSP_yr1, PH_count_COMMHOSP_yr1, PH_dummy_COMMHOSP_cum2, PH_count_COMMHOSP_cum2, PH_dummy_COMMHOSP_cum3, PH_count_COMMHOSP_cum3,
	MH_dummy_COMMED_lag1, MH_count_COMMED_lag1, MH_dummy_COMMED_yr1, MH_count_COMMED_yr1, MH_dummy_COMMED_cum2, MH_count_COMMED_cum2, MH_dummy_COMMED_cum3, MH_count_COMMED_cum3, PH_dummy_COMMED_lag1, PH_count_COMMED_lag1, PH_dummy_COMMED_yr1, PH_count_COMMED_yr1, PH_dummy_COMMED_cum2, PH_count_COMMED_cum2, PH_dummy_COMMED_cum3, PH_count_COMMED_cum3,
	MH_dummy_COMMUCC_lag1, MH_count_COMMUCC_lag1, MH_dummy_COMMUCC_yr1, MH_count_COMMUCC_yr1, MH_dummy_COMMUCC_cum2, MH_count_COMMUCC_cum2, MH_dummy_COMMUCC_cum3, MH_count_COMMUCC_cum3, PH_dummy_COMMUCC_lag1, PH_count_COMMUCC_lag1, PH_dummy_COMMUCC_yr1, PH_count_COMMUCC_yr1, PH_dummy_COMMUCC_cum2, PH_count_COMMUCC_cum2, PH_dummy_COMMUCC_cum3, PH_count_COMMUCC_cum3,
	MH_dummy_CMSHOSP_lag1, MH_count_CMSHOSP_lag1, MH_dummy_CMSHOSP_yr1, MH_count_CMSHOSP_yr1, MH_dummy_CMSHOSP_cum2, MH_count_CMSHOSP_cum2, MH_dummy_CMSHOSP_cum3, MH_count_CMSHOSP_cum3, PH_dummy_CMSHOSP_lag1, PH_count_CMSHOSP_lag1, PH_dummy_CMSHOSP_yr1, PH_count_CMSHOSP_yr1, PH_dummy_CMSHOSP_cum2, PH_count_CMSHOSP_cum2, PH_dummy_CMSHOSP_cum3, PH_count_CMSHOSP_cum3,
	MH_dummy_CMSED_lag1, MH_count_CMSED_lag1, MH_dummy_CMSED_yr1, MH_count_CMSED_yr1, MH_dummy_CMSED_cum2, MH_count_CMSED_cum2, MH_dummy_CMSED_cum3, MH_count_CMSED_cum3, PH_dummy_CMSED_lag1, PH_count_CMSED_lag1, PH_dummy_CMSED_yr1, PH_count_CMSED_yr1, PH_dummy_CMSED_cum2, PH_count_CMSED_cum2, PH_dummy_CMSED_cum3, PH_count_CMSED_cum3,
	MH_dummy_CMSUCC_lag1, MH_count_CMSUCC_lag1, MH_dummy_CMSUCC_yr1, MH_count_CMSUCC_yr1, MH_dummy_CMSUCC_cum2, MH_count_CMSUCC_cum2, MH_dummy_CMSUCC_cum3, MH_count_CMSUCC_cum3, PH_dummy_CMSUCC_lag1, PH_count_CMSUCC_lag1, PH_dummy_CMSUCC_yr1, PH_count_CMSUCC_yr1, PH_dummy_CMSUCC_cum2, PH_count_CMSUCC_cum2, PH_dummy_CMSUCC_cum3, PH_count_CMSUCC_cum3
INTO #threeyear_outcomes
FROM #mh_agg
FULL OUTER JOIN #ph_agg ON #mh_agg.PatientICN=#ph_agg.PatientICN 


SELECT 
	#cohort_final.*, 
	MH_dummy_HOSP_lag1, MH_count_HOSP_lag1, MH_dummy_HOSP_yr1, MH_count_HOSP_yr1, MH_dummy_HOSP_cum2, MH_count_HOSP_cum2, MH_dummy_HOSP_cum3, MH_count_HOSP_cum3, PH_dummy_HOSP_lag1, PH_count_HOSP_lag1, PH_dummy_HOSP_yr1, PH_count_HOSP_yr1, PH_dummy_HOSP_cum2, PH_count_HOSP_cum2, PH_dummy_HOSP_cum3, PH_count_HOSP_cum3,
	MH_dummy_ED_lag1, MH_count_ED_lag1, MH_dummy_ED_yr1, MH_count_ED_yr1, MH_dummy_ED_cum2, MH_count_ED_cum2, MH_dummy_ED_cum3, MH_count_ED_cum3, PH_dummy_ED_lag1, PH_count_ED_lag1, PH_dummy_ED_yr1, PH_count_ED_yr1, PH_dummy_ED_cum2, PH_count_ED_cum2, PH_dummy_ED_cum3, PH_count_ED_cum3,
	MH_dummy_UCC_lag1, MH_count_UCC_lag1, MH_dummy_UCC_yr1, MH_count_UCC_yr1, MH_dummy_UCC_cum2, MH_count_UCC_cum2, MH_dummy_UCC_cum3, MH_count_UCC_cum3, PH_dummy_UCC_lag1, PH_count_UCC_lag1, PH_dummy_UCC_yr1, PH_count_UCC_yr1, PH_dummy_UCC_cum2, PH_count_UCC_cum2, PH_dummy_UCC_cum3, PH_count_UCC_cum3,
	MH_dummy_COMMHOSP_lag1, MH_count_COMMHOSP_lag1, MH_dummy_COMMHOSP_yr1, MH_count_COMMHOSP_yr1, MH_dummy_COMMHOSP_cum2, MH_count_COMMHOSP_cum2, MH_dummy_COMMHOSP_cum3, MH_count_COMMHOSP_cum3, PH_dummy_COMMHOSP_lag1, PH_count_COMMHOSP_lag1, PH_dummy_COMMHOSP_yr1, PH_count_COMMHOSP_yr1, PH_dummy_COMMHOSP_cum2, PH_count_COMMHOSP_cum2, PH_dummy_COMMHOSP_cum3, PH_count_COMMHOSP_cum3,
	MH_dummy_COMMED_lag1, MH_count_COMMED_lag1, MH_dummy_COMMED_yr1, MH_count_COMMED_yr1, MH_dummy_COMMED_cum2, MH_count_COMMED_cum2, MH_dummy_COMMED_cum3, MH_count_COMMED_cum3, PH_dummy_COMMED_lag1, PH_count_COMMED_lag1, PH_dummy_COMMED_yr1, PH_count_COMMED_yr1, PH_dummy_COMMED_cum2, PH_count_COMMED_cum2, PH_dummy_COMMED_cum3, PH_count_COMMED_cum3,
	MH_dummy_COMMUCC_lag1, MH_count_COMMUCC_lag1, MH_dummy_COMMUCC_yr1, MH_count_COMMUCC_yr1, MH_dummy_COMMUCC_cum2, MH_count_COMMUCC_cum2, MH_dummy_COMMUCC_cum3, MH_count_COMMUCC_cum3, PH_dummy_COMMUCC_lag1, PH_count_COMMUCC_lag1, PH_dummy_COMMUCC_yr1, PH_count_COMMUCC_yr1, PH_dummy_COMMUCC_cum2, PH_count_COMMUCC_cum2, PH_dummy_COMMUCC_cum3, PH_count_COMMUCC_cum3,
	MH_dummy_CMSHOSP_lag1, MH_count_CMSHOSP_lag1, MH_dummy_CMSHOSP_yr1, MH_count_CMSHOSP_yr1, MH_dummy_CMSHOSP_cum2, MH_count_CMSHOSP_cum2, MH_dummy_CMSHOSP_cum3, MH_count_CMSHOSP_cum3, PH_dummy_CMSHOSP_lag1, PH_count_CMSHOSP_lag1, PH_dummy_CMSHOSP_yr1, PH_count_CMSHOSP_yr1, PH_dummy_CMSHOSP_cum2, PH_count_CMSHOSP_cum2, PH_dummy_CMSHOSP_cum3, PH_count_CMSHOSP_cum3,
	MH_dummy_CMSED_lag1, MH_count_CMSED_lag1, MH_dummy_CMSED_yr1, MH_count_CMSED_yr1, MH_dummy_CMSED_cum2, MH_count_CMSED_cum2, MH_dummy_CMSED_cum3, MH_count_CMSED_cum3, PH_dummy_CMSED_lag1, PH_count_CMSED_lag1, PH_dummy_CMSED_yr1, PH_count_CMSED_yr1, PH_dummy_CMSED_cum2, PH_count_CMSED_cum2, PH_dummy_CMSED_cum3, PH_count_CMSED_cum3,
	MH_dummy_CMSUCC_lag1, MH_count_CMSUCC_lag1, MH_dummy_CMSUCC_yr1, MH_count_CMSUCC_yr1, MH_dummy_CMSUCC_cum2, MH_count_CMSUCC_cum2, MH_dummy_CMSUCC_cum3, MH_count_CMSUCC_cum3, PH_dummy_CMSUCC_lag1, PH_count_CMSUCC_lag1, PH_dummy_CMSUCC_yr1, PH_count_CMSUCC_yr1, PH_dummy_CMSUCC_cum2, PH_count_CMSUCC_cum2, PH_dummy_CMSUCC_cum3, PH_count_CMSUCC_cum3
INTO OMHO_QFR.ECON.appointments
FROM #cohort_final
LEFT OUTER JOIN #threeyear_outcomes
	ON #cohort_final.PatientICN=#threeyear_outcomes.PatientICN

CREATE CLUSTERED INDEX PatientICN 
ON OMHO_QFR.ECON.appointments (PatientICN ASC)
WITH (SORT_IN_TEMPDB = ON, 
      ONLINE = OFF, 
      FILLFACTOR = 100, 
      DATA_COMPRESSION = PAGE) 
ON [DefFG];




